CODEWITHSUNDEEP
sqlpostgresqlforeign-keys
Andrus
Andrus

Reputation: 27955

How to add foreign key only if it does not exist

PostgreSQL database upgrade script adds foreign key constraint using commands:

update omrid set tellkanne=null where tellkanne =0 or tellkanne not in (select dokumnr from dok);

alter table omrid ADD constraint omrid_tellkanne_fkey  FOREIGN KEY (tellkanne)
 REFERENCES dok(dokumnr) ON UPDATE CASCADE on delete set null DEFERRABLE

dokumnr is dot table primary key with serial type. both fields are int type

It takes lot of time if foreign key is already added before error is reported. How to run those commands only if foreign key omrid_tellkanne_fkey does not exist ?

Upvotes: 0

Views: 2603

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95761

You can query one of the information_schema views to see whether a constraint by that name exists.

select constraint_name 
from information_schema.referential_constraints
where constraint_name = 'omrid_tellkanne_fkey';

Whether this works reliably is application-dependent. Although this will reliably tell you whether a foreign key constraint named 'omrid_tellkanne_fkey' exists, it won't tell you whether that constraint is a foreign key constraint between the tables you're interested in. You'll need to join one or two more tables from the information_schema views to get that.

A more general query might look like this.

select k.table_catalog || '.' ||
       k.table_schema || '.' ||
       k.table_name referencing_table,
       r.constraint_name,
       c.table_catalog || '.' ||
       c.table_schema || '.' ||
       c.table_name referenced_table
from information_schema.referential_constraints r
inner join information_schema.key_column_usage k
  on r.constraint_catalog = k.constraint_catalog
  and r.constraint_schema = k.constraint_schema
  and r.constraint_name = k.constraint_name
inner join information_schema.constraint_column_usage c
  on r.constraint_catalog = c.constraint_catalog
  and r.constraint_schema = c.constraint_schema
  and r.constraint_name = c.constraint_name
-- Change this WHERE clause to match your object names. 
where r.constraint_catalog = 'your_database'
  and r.constraint_schema = 'your_schema'
  and r.constraint_name = 'omrid_tellkanne_fkey'
  and k.table_name = 'omrid'
  and c.table_name = 'dok';

Upvotes: 0

Related Questions