Re: BUG #16767: Silent dropping of CONSTRAINT... UNIQUE

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> I've just noticed that equivalent unique constraints that are specified in
> the same statement only generate one constraint;

Yeah, that's intentional. Per the source code comments:

* Scan the index list and remove any redundant index specifications. This
* can happen if, for instance, the user writes UNIQUE PRIMARY KEY. A
* strict reading of SQL would suggest raising an error instead, but that
* strikes me as too anal-retentive. - tgl 2001-02-14

The CREATE TABLE man page does explain this with respect to primary keys:

The primary key constraint should name a set of columns that is
different from the set of columns named by any unique
constraint defined for the same table. (Otherwise, the unique
constraint is redundant and will be discarded.)

However, I see that there's not similar wording under UNIQUE; that says

Each unique table constraint must name a set of columns that is
different from the set of columns named by any other unique or
primary key constraint defined for the table. (Otherwise it
would just be the same constraint listed twice.)

That implies that such a constraint is redundant, but it doesn't actually
say it in so many words. We should probably use wording more like the
PRIMARY KEY text.

regards, tom lane