Re: Reject ADD CONSTRAINT NOT NULL if name mismatches existing domain not-null constraint

On 2026-Mar-02, Chao Li wrote:

> But when I played with the patch, I saw a problem. In the test script, we can see:
> ```
> alter domain connotnull add constraint constr1 not null;
> alter domain connotnull add constraint constr1 not null; — redundant
> ```
>
> If we first create a named constraint “constr1” then create an unnamed one, that’s fine, the unnamed is considered as redundant. However, if I do the reverse order, add a unnamed first, then “constr1”, it failed:
> ```
> evantest=# create domain connotnull integer;
> CREATE DOMAIN
> evantest=# alter domain connotnull add not null;
> ALTER DOMAIN
> evantest=# alter domain connotnull add constraint constr1 not null;
> ERROR: cannot create not-null constraint "constr1" for domain "connotnull"
> DETAIL: A not-null constraint named "connotnull_not_null" already exists for domain "connotnull".
> ```
>
> Is that an expected behavior?

Yes. A column or domain can only have one not-null constraint, and the
default name is not going to match "constr1", so if you request constr1
first, then that's okay because the second unnamed one matches the
constraint; but if you request the unnamed first it'll get the default
name and when you next request "constr1", that won't match the name that
was defaulted.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"La espina, desde que nace, ya pincha" (Proverbio africano)