0

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ᵀᴹ Oct 17 '16 at 17:11
  • is he saying there is no solution ? – amphibient Oct 17 '16 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 – dezso Oct 17 '16 at 20:44
  • @dezso - 9.0.13 but I think the DBA actually assigned poorly chosen names (duplicates) – amphibient Oct 17 '16 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). – dezso Oct 17 '16 at 21:04
4

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, privacy policy and cookie policy

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