If I read the spec correctly, table constraint names are supposed to be
unique across a schema. So technically the constraint name should also
not conflict with the name of an fk constraint, or a unique index. In
addition, generated constraint names are supposed to follow the same
syntax rules (which includes the uniqueness) which seems to imply that
in cases like the below, that's not an error, and a different name should
be generated for the unnamed constraint. However, unnamed column
constraints seem to get the empty string as a name.
I'd say don't worry about it for the purposes of drop constraint. :)
On Fri, 4 May 2001, Christopher Kings-Lynne wrote:
> Hi,
>
> I have noticed that it is possible to create duplicate CHECK (haven't tried
> other) constraints in 7.0.3 by doing something like this:
>
> CREATE TABLE "test" (
> "a" int4,
> CHECK (a < 400),
> CONSTRAINT "$1" CHECK (a > 5)
> );
>
> I was just fiddling around with trying to implement the 'DROP CONSTRAINT'
> code (it's quite hard - don't wait up for me!) and it would seem to be a bad
> thing that it's possible to have two constraints with the same name in a
> table.
>
> Surely there should be a UNIQUE (rcrelid, rcname) on pg_relcheck?, or at
> least better checking in the CREATE TABLE code?