PostgreSQL – Enforce Constraint Name Uniqueness

postgresql

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?

Best Answer

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

Related Solutions

PostgreSQL Alias – Why GROUP BY is Required?

The problem is that the 2 tables are not identical.

The first has (id) as the PRIMARY KEY while the second has (id) a UNIQUE constraint (or index, doesn't matter). This might seem like a minor detail but it isn't.

Postgres has added in version 9.1, a feature were functionally dependent columns need not be mentioned in a GROUP BY clause (if the column they depend on is mentioned in the GROUP BY) and still used in the HAVING, SELECT and ORDER BY clauses. However the implementation is not 100% complete. It doesn't identify all possible functional dependencies but only those coming from PRIMARY KEY constraints. While a UNIQUE constraint with all columns NOT NULL does not differ in any meaningful way from a PRIMARY KEY constraint, the implementation does not consider UNIQUE constraints.

So, for example, with these two very similar tables (test in SQLfiddle):

CREATE TABLE products
  ( id INT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    PRIMARY KEY (id)
  ) ;

CREATE TABLE products_active
  ( id INT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    UNIQUE (id)
  ) ;

The query will succeed for the first table:

SELECT p.id, p.status, p.name
FROM products AS p
GROUP BY p.id ;

while it will fail for the second:

SELECT p.id, p.status, p.name
FROM products_active AS p
GROUP BY p.id ;

with:

ERROR: column "p.status" must appear in the GROUP BY clause or be used in an aggregate function

How to Delete Aged PostgreSQL Rows with Foreign Key Constraints

First start a transaction:

begin;

Start deleting all from links_groupseen first:

delete 
from links_groupseen 
where which_reply_id in (select myproperid from links_reply where "submitted_on" < now() - interval '30 days')

and then delete from links_reply:

DELETE 
FROM links_reply 
WHERE "submitted_on" < now()- interval '30 days';

and if you're happy with what you see, you can COMMIT. The reason I'm using a transaction is that within a transaction, now() will give you the same values at each call. Otherwise, you would have to replace now with a constant MYDATE value of your choice on both delete statements.

Best Answer

Related Solutions

PostgreSQL Alias – Why GROUP BY is Required?

How to Delete Aged PostgreSQL Rows with Foreign Key Constraints

Related Question