How to create foreign key only if it doesn't exists already?

Question

I'm using PostgreSQL 9.1.

I have a table common.client_contact where I created foreign key using this code:

ALTER TABLE common.client_contact 
ADD FOREIGN KEY (contact_id) REFERENCES common.contact_item(id);

If I execute this code, I will get several foreign keys with different names (like client_contact_contact_id_fkey1, client_contact_contact_id_fkey2, client_contact_contact_id_fkey3 and so on).

So, before creating new constraint, I need to check if it's exists.

I check if this constraint exists in pg_constraint table:

SELECT * FROM pg_constraint WHERE conname = 'client_contact_contact_id_fkey'

And now I need to combine them together. Something like

IF NOT EXISTS
    (SELECT * FROM pg_constraint WHERE conname = 'client_contact_contact_id_fkey')
ALTER TABLE common.client_contact
    ADD CONSTRAINT client_contact_contact_id_fkey
    FOREIGN KEY (contact_id) REFERENCES common.contact_item(id)

or just

ALTER TABLE common.client_contact 
ADD FOREIGN KEY IF NOT EXISTS (contact_id) REFERENCES common.contact_item(id)

But these two queries are produce syntax error. So, how can I do it in PostgreSQL?

Answer 1

Use a DO block to execute it in PL/PgSQL.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'client_contact_contact_id_fkey') THEN
        ALTER TABLE common.client_contact
            ADD CONSTRAINT client_contact_contact_id_fkey
            FOREIGN KEY (contact_id) REFERENCES common.contact_item(id);
    END IF;
END;
$$;

You seem to be relying on the default constraint name generation, which isn't ideal. It's probably safer to use information_schema to check for the presence of a constraint that links the two columns.

The following query checks for a foreign key between the two tables without relying on the generated constraint name:

SELECT 1
FROM information_schema.table_constraints tc 
INNER JOIN information_schema.constraint_column_usage ccu 
  USING (constraint_catalog, constraint_schema, constraint_name) 
INNER JOIN information_schema.key_column_usage kcu 
  USING (constraint_catalog, constraint_schema, constraint_name) 
WHERE constraint_type = 'FOREIGN KEY' 
  AND ccu.table_name = 'contact_item' 
  AND ccu.table_schema = 'common'  
  AND ccu.column_name = 'contact_id' 
  AND tc.table_schema = 'common' 
  AND tc.table_name = 'client_contact'
  AND kcu.column_name = 'id';