From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
cc: =?utf-8?B?QW5kcsOpIEjDpG5zZWw=?= <andre@webkr.de>,
        pgsql-bugs@postgresql.org
Subject: Re: Two constraints with the same name not always allowed
In-reply-to: <10110.1535907645@sss.pgh.pa.us>
References: <0c1001d4428f$0942b430$1bc81c90$@webkr.de>
 <87in3ovy0r.fsf@news-spur.riddles.org.uk> <10110.1535907645@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Sun, 02 Sep 2018 13:00:45 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <13717.1535912142.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Sun, 02 Sep 2018 14:15:42 -0400
Message-ID: <13718.1535912142@sss.pgh.pa.us>
Precedence: bulk

I wrote:
> Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
>> "André" == André Hänsel <andre@webkr.de> writes:
>> André> Case 2:
>> André> CREATE TABLE t(c integer);
>> André> ALTER TABLE t ADD CONSTRAINT foo CHECK(c > 1);
>> André> ALTER TABLE t ADD CONSTRAINT foo UNIQUE(c);
>> André>  -> 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.

> Agreed.  We must have missed a check for constraint-exists someplace.

Note that if you repeat that last command, what you get is

regression=# ALTER TABLE t ADD CONSTRAINT foo UNIQUE(c);
ERROR:  relation "foo" already exists

I think the code supposes that checking for duplicate relation name
is sufficient; but of course it is not if we want a table's constraints
to have distinct names, since they may not all correspond to indexes.

I do not think we can back-patch a change here --- it might break
databases that are working satisfactorily today.  But it seems like
we could tighten this up in HEAD and maybe v11.

			regards, tom lane