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?

Craig Ringer · Accepted Answer · 2014-07-25T23:39:49.373

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';

BTW, I suspect the query above needs refining to make sure it doesn't match multicolumn constraints that *include* `contact_id` and `id`, so test further if you want to use it. — Craig Ringer, Oct 12 '12 at 09:49
In the first part of code snippet, should it not be "IF NOT EXISTS" instead of "IF EXISTS"? — Kumar Vaibhav, Jul 25 '14 at 15:40
You can also use an exception handler instead of the if not exists. See my answer to http://stackoverflow.com/questions/6801919/postgres-add-constraint-if-it-doesnt-already-exist/32526723#32526723 — Mike Stankavich, Sep 11 '15 at 15:12

score 9 · Answer 2 · edited Nov 29 '20 at 15:24

9

one way around the issue you are having is to delete the constraint before you create it

ALTER TABLE common.client_contact DROP CONSTRAINT IF EXISTS  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)

The adding of the named constraint will pass.

edited Nov 29 '20 at 15:24

Youcef LAIDANI

55,661
15
90
140

answered Apr 03 '17 at 18:33

dkrebbers

99
1
3

score -11 · Answer 3 · answered Oct 12 '12 at 09:14

-11

I don't know for sure if this will work but you can try it.

Fires, before you do the query

SET FOREIGN_KEY_CHECKS=0

Then you make the query and

SET FOREIGN_KEY_CHECKS=1

answered Oct 12 '12 at 09:14

Elkas

603
12
27

PostgreSQL doesn't even *have* a `FOREIGN_KEY_CHECKS` GUC, so I don't know where you got that one. Second, this isn't about temporarily violating a foreign key (a terrible idea anyway), it's about defining a new foreign key constraint only if one doesn't already exist. – Craig Ringer Oct 12 '12 at 09:48
Thats I said, "I don't know for sure if this will work but you can try it." – Elkas Oct 17 '12 at 09:50

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

3 Answers3