How to create foreign key only if it doesn't exists already?
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';
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.