Re: Two constraints with the same name not always allowed

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> "André" == André Hänsel <andre(at)webkr(dot)de> writes:
> André> Case 2:
> André> CREATE TABLE t(c integer);
> André> ALTER TABLE t ADD CONSTRAINT foo CHECK(c > 1);
> André> ALTER TABLE t ADD CONSTRAINT foo UNIQUE(c);
> André> -> Creates two constraints, both called "foo".

> I'd call _that_ a bug, myself - having two constraints on a table with
> the same name potentially messes up a lot of automated maintenance
> operations.

Agreed. We must have missed a check for constraint-exists someplace.

This also points up the lack of a suitable unique index on pg_constraint.
It's sort of difficult to figure out what that should look like given that
pg_constraint contains two quasi-independent collections of constraints,
but maybe UNIQUE(conrelid,contypid,conname) would serve given the
reasonable assumption that exactly one of conrelid and contypid is zero.

Potentially we could drop pg_constraint_conrelid_index and
pg_constraint_contypid_index, replacing scans on those with
scans on this new unique index.

regards, tom lane