How to Check if a Foreign Key Exists on a Specific Table in PostgreSQL

Question

I have a foreign key named user__fk__store_id that was supposed to be created on the user table.

However, I made a mistake and instead have it created on another table I have named client.

My servers have an automated process that reads from a JSON file I created with what new tables to create, remove, etc... Every time a server needs to be upgraded with new stuff, it will run through this JSON file and run the queries it needs to.

In this case, in the json file, I'm trying to have it drop the existing incorrect foreign key constraint that was created on the client table, and recreate it correctly on the user table. So technically, it should be running these 2 queries back to back:

ALTER TABLE client DROP CONSTRAINT user__fk__store_id;
ALTER TABLE user ADD CONSTRAINT user__fk__store_id;

The problem I'm having is I can't figure out the query to run in order to see if the user__fk__store_id exists on the client table. I only know how to check if the constraint exists on any table in the database with the following:

SELECT COUNT(1) FROM pg_constraint WHERE conname='user__fk__store_id';

This would be a problem because this means every time I run my upgrade script on my servers, it will always think the constraint of that name already exists, but when it attempts to run the drop query it will error out because it can't find that constraint in the client table.

Is there a query I can run to check not just if the constraint exists, but also if it exists in a specific table?

Answer 1

I found the answer to my own question, I can just run the following query:

SELECT COUNT(1) FROM information_schema.table_constraints WHERE constraint_name='user__fk__store_id' AND table_name='client';