Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fwSLQ-00069R-BP for pgsql-bugs@arkaria.postgresql.org; Sun, 02 Sep 2018 13:23:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fwSLO-0005AW-HV for pgsql-bugs@arkaria.postgresql.org; Sun, 02 Sep 2018 13:23:10 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fwSLO-0005AP-88 for pgsql-bugs@lists.postgresql.org; Sun, 02 Sep 2018 13:23:10 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fwSLG-0007JC-Tg for pgsql-bugs@postgresql.org; Sun, 02 Sep 2018 13:23:09 +0000 Received: from [192.168.127.1] (port=31618 helo=caithnard.riddles.org.uk) by lungold.riddles.org.uk with esmtps (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256) (Exim 4.88 (FreeBSD)) (envelope-from ) id 1fwSLC-000Kn2-NP; Sun, 02 Sep 2018 13:22:58 +0000 Received: from [127.0.0.1] (port=16234 helo=caithnard.riddles.org.uk) by caithnard.riddles.org.uk with esmtp (Exim 4.89 (FreeBSD)) (envelope-from ) id 1fwSLB-00061T-OW; Sun, 02 Sep 2018 13:22:57 +0000 From: Andrew Gierth To: =?utf-8?B?QW5kcsOpIEjDpG5zZWw=?= Cc: Subject: Re: Two constraints with the same name not always allowed In-Reply-To: <0c1001d4428f$0942b430$1bc81c90$@webkr.de> (=?utf-8?Q?=22Andr?= =?utf-8?Q?=C3=A9_H=C3=A4nsel=22's?= message of "Sun, 2 Sep 2018 09:31:52 +0200") Message-ID: <87in3ovy0r.fsf@news-spur.riddles.org.uk> References: <0c1001d4428f$0942b430$1bc81c90$@webkr.de> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix) Date: Sun, 02 Sep 2018 14:22:56 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk >>>>> "Andr=C3=A9" =3D=3D Andr=C3=A9 H=C3=A4nsel writes: Andr=C3=A9> Case 2: Andr=C3=A9> CREATE TABLE t(c integer); Andr=C3=A9> ALTER TABLE t ADD CONSTRAINT foo CHECK(c > 1); Andr=C3=A9> ALTER TABLE t ADD CONSTRAINT foo UNIQUE(c); Andr=C3=A9> -> 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. Andr=C3=A9> In cases where two constraints with the same name are created, Andr=C3=A9> an "ALTER TABLE t DROP CONSTRAINT ..." drops the UNIQUE first. Andr=C3=A9> Issuing the ALTER TABLE a second time then drops the CHECK. I think that's purely an artifact of what order an index scan on pg_constraint_conrelid_index finds the constraints. --=20 Andrew (irc:RhodiumToad)