pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: boekewurm+postgres@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16767: Silent dropping of CONSTRAINT... UNIQUE
Date: Tue, 08 Dec 2020 11:48:01 -0500
Message-ID: <3254991.1607446081@sss.pgh.pa.us> (raw)
In-Reply-To: <16767-1714a2056ca516d0@postgresql.org>
References: <16767-1714a2056ca516d0@postgresql.org>

PG Bug reporting form <noreply@postgresql.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





view thread (4+ messages)  latest in thread

Message-ID: <3254991.1607446081@sss.pgh.pa.us>
Permalink:  ../3254991.1607446081@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3254991.1607446081@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, boekewurm+postgres@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #16767: Silent dropping of CONSTRAINT... UNIQUE
  In-Reply-To: <3254991.1607446081@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox