I just noticed that PostgreSQL allows repeating foreign key (and possibly other constraint) names across the database as long as they are on a different table.

So if the parent table is Foo and it has two child tables BarX and BarY, the foreign keys on both tables can be named FK_fooid.

This, IMO, is a terrible design because

select * from information_schema.referential_constraints
where constraint_name = 'FK_fooid' 

will return two identical rows with no ability to differentiate which one corresponds to which table/key.

Is there a way to disable repetition of constraint names across a database in Postgres and to enforce that each constraint has a unique name?

  • 1
    This (and the reply) sums it up: postgresql.org/message-id/…
    – Philᵀᴹ
    Commented Oct 17, 2016 at 17:11
  • is he saying there is no solution ?
    – amphibient
    Commented Oct 17, 2016 at 17:16
  • Which version are you on? Since 8.0 the automatically created constraint name should be different inside a schema - on my box they look like {tablename}_{columname}_fkey. See also the note at postgresql.org/docs/9.6/static/information-schema.html Commented Oct 17, 2016 at 20:44
  • @dezso - 9.0.13 but I think the DBA actually assigned poorly chosen names (duplicates)
    – amphibient
    Commented Oct 17, 2016 at 20:58
  • I see. In any case, tell your DBA to upgrade to a supported version (9.1+ currently). If it is impossible for some reason, upgrade to the latest minor version of 9.0 (9.0.23). Commented Oct 17, 2016 at 21:04

1 Answer 1


will return two IDENTICAL rows with no ability to differentiate which one corresponds to which table/key.

Then don't use information_schema:

select nsp.nspname as constraint_schema,
       c.conname as constraint_name,  
       format('%I.%I', ts.nspname, t.relname) as target_table,
       pg_get_constraintdef(c.oid) as constraint_definition
from pg_class t 
  join pg_constraint c on t.oid = c.conrelid   
  join pg_namespace nsp on t.relnamespace = nsp.oid 
  join pg_namespace ts on t.relnamespace = ts.oid
where c.contype in ('f') 
 and c.conname = 'fk_fooid';

Example: http://rextester.com/DAR30737

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.