Re: Wrong note in the information schema section?

On Tue, Aug 31, 2021 at 6:53 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Monday, August 30, 2021, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
>> On Monday, August 30, 2021, Daniel Westermann (DWE) <
>> daniel(dot)westermann(at)dbi-services(dot)com> wrote:
>>
>>>
>>> >Practically speaking there must be some level of scope where a
>>> duplicate name error can occur. All the docs say is that the schema >scope
>>> is not it. You've demonstrated that it is the table scope where
>>> duplication of names is detected.
>>>
>>> Thanks, David. The sentence above is still misleading, at least
>>> according to my understanding.
>>
>>
>> Create a second table and add a constraint of the same name to it.
>>
>>
>
> And your error is actually because the name of the unique index backing
> the constraint is a problem, not the name of the constraint itself. Try
> naming a check constraint.
>
> David J.
>
>

As the rest of the paragraph explains, name duplication is checked at table
level for all constraints and at schema level for index-based constraints
(UNIQUE, PRIMARY KEY, EXCLUDE):

> > ... However, this extra freedom does not exist for index-based
constraints (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the
associated index is named the same as the constraint, and index names must
be unique across all relations within the same schema.

So, adding two index-based constraints (UNIQUE or PK or EXCLUDE) with same
name fails, whether they are in the same table or different ones in the
same schema.
Adding two constraints (whatever type) with same name in the same table
fails.
Adding two or more constraints with same name in different tables of the
same schema succeeds as long as none or only one is index-based.

Best regards,
Pantelis Theodosiou