Two constraints with the same name not always allowed

From: André Hänsel <andre(at)webkr(dot)de>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Two constraints with the same name not always allowed
Date: 2018-09-02 07:31:52
Message-ID: 0c1001d4428f$0942b430$1bc81c90$@webkr.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi list,
calling this a bug might be pedantic, but I noticed this inconsistency:

Case 1:

CREATE TABLE t(c integer);
ALTER TABLE t ADD CONSTRAINT foo UNIQUE(c);
ALTER TABLE t ADD CONSTRAINT foo CHECK(c > 1);

-> ERROR: constraint "foo" for relation "t" already exists

Case 2:

CREATE TABLE t(c integer);
ALTER TABLE t ADD CONSTRAINT foo CHECK(c > 1);
ALTER TABLE t ADD CONSTRAINT foo UNIQUE(c);

-> Creates two constraints, both called "foo".

Case 3:

CREATE TABLE t(c integer);
ALTER TABLE t ADD CONSTRAINT t_c_check UNIQUE(c); -- add the UNIQUE with the
same name that the following CHECK will automatically choose
ALTER TABLE t ADD CHECK(c > 1);

-> Creates the UNIQUE constraint as "t_c_check" and the CHECK as
"t_c_check1"

Case 4:

CREATE TABLE t(c integer);
ALTER TABLE t ADD CONSTRAINT t_c_key CHECK(c > 1);
ALTER TABLE t ADD UNIQUE(c);

-> Creates two constraints, both called "t_c_key".

In cases where two constraints with the same name are created, an "ALTER
TABLE t DROP CONSTRAINT ..." drops the UNIQUE first. Issuing the ALTER TABLE
a second time then drops the CHECK.

Regards,
André

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Olivier Leprêtre 2018-09-02 09:20:47 RE: BUG #15361: Add column if not exists create duplicate constraint
Previous Message 1111indiana 2018-09-02 06:27:26 Cache lookup failed for function 1 while enabling postgis on postgres 10