Enforce constraint name uniqueness

Question

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?

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