Обсуждение: Reject ADD CONSTRAINT NOT NULL if name mismatches existing domain not-null constraint

Hi.

Similar to https://git.postgresql.org/cgit/postgresql.git/commit/?id=96e2af605043974137d84edf5c0a24561956919e
We apply this logic to the domain not-null constraint too.
It would error out if ALTER DOMAIN ADD CONSTRAINT NOT NULL, the new
constraint name does not
matches the existing domain's not-null constraint

create domain d1 as int constraint nn not null;
src4=# alter domain d1 add constraint nn1 not null;
ERROR:  cannot create not-null constraint "nn1" for domain "d1"
DETAIL:  A not-null constraint named "nn" already exists for domain "d1".

However, repeated ALTER DOMAIN SET NOT NULL or ALTER DOMAIN ADD NOT
NULL statements are allowed,
This aligns with the NOT NULL constraints on tables.

No need to worry about CREATE DOMAIN.
We already disallow multiple NOT NULL constraints in CREATE DOMAIN.
Like this would fail:
create domain d2 as text collate "C" constraint nn not null constraint
nn2 not null;




--
jian
https://www.enterprisedb.com/

> On Mar 1, 2026, at 09:53, jian he <jian.universality@gmail.com> wrote:
>
> Hi.
>
> Similar to https://git.postgresql.org/cgit/postgresql.git/commit/?id=96e2af605043974137d84edf5c0a24561956919e
> We apply this logic to the domain not-null constraint too.
> It would error out if ALTER DOMAIN ADD CONSTRAINT NOT NULL, the new
> constraint name does not
> matches the existing domain's not-null constraint
>
> create domain d1 as int constraint nn not null;
> src4=# alter domain d1 add constraint nn1 not null;
> ERROR:  cannot create not-null constraint "nn1" for domain "d1"
> DETAIL:  A not-null constraint named "nn" already exists for domain "d1".
>
> However, repeated ALTER DOMAIN SET NOT NULL or ALTER DOMAIN ADD NOT
> NULL statements are allowed,
> This aligns with the NOT NULL constraints on tables.
>
> No need to worry about CREATE DOMAIN.
> We already disallow multiple NOT NULL constraints in CREATE DOMAIN.
> Like this would fail:
> create domain d2 as text collate "C" constraint nn not null constraint
> nn2 not null;
>
>
>
>
> --
> jian
> https://www.enterprisedb.com/
> <v1-0001-Reject-ADD-CONSTRAINT-NOT-NULL-if-name-mismatches-existing-domain.patch>


The code change looks good to me. This patch uses the same error code and similar error message as
96e2af605043974137d84edf5c0a24561956919e,so they should be fine. I saw conTup returned from
findDomainNotNullConstraint()is not free-ed, but that seems as as we can rely on memory context to free the memory. 

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?

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

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)