Re: Catalog domain not-null constraints

On 20.03.24 12:22, Dean Rasheed wrote:
> Hmm, for CHECK constraints, the ALTER DOMAIN syntax for adding a
> constraint is the same as for CREATE DOMAIN, but that's not the case
> for NOT NULL constraints. So, for example, these both work:
> 
> CREATE DOMAIN d AS int CONSTRAINT c1 CHECK (value > 0);
> 
> ALTER DOMAIN d ADD CONSTRAINT c2 CHECK (value < 10);
> 
> However, for NOT NULL constraints, the ALTER DOMAIN syntax differs
> from the CREATE DOMAIN syntax, because it expects "NOT NULL" to be
> followed by a column name. So the following CREATE DOMAIN syntax
> works:
> 
> CREATE DOMAIN d AS int CONSTRAINT nn NOT NULL;
> 
> but the equivalent ALTER DOMAIN syntax doesn't work:
> 
> ALTER DOMAIN d ADD CONSTRAINT nn NOT NULL;
> 
> ERROR:  syntax error at or near ";"
> LINE 1: ALTER DOMAIN d ADD CONSTRAINT nn NOT NULL;
>                                                   ^
> 
> All the examples in the tests append "value" to this, presumably by
> analogy with CHECK constraints, but it looks as though anything works,
> and is simply ignored:
> 
> ALTER DOMAIN d ADD CONSTRAINT nn NOT NULL xxx; -- works
> 
> That doesn't seem particularly satisfactory. I think it should not
> require (and reject) a column name after "NOT NULL".

Hmm.  CREATE DOMAIN uses column constraint syntax, but ALTER DOMAIN uses 
table constraint syntax.  As long as you are only dealing with CHECK 
constraints, there is no difference, but it shows up when using NOT NULL 
constraint syntax.  I agree that this is unsatisfactory.  Attached is a 
patch to try to sort this out.

> Looking in the SQL spec, it seems to only mention adding CHECK
> constraints to domains, so the option to add NOT NULL constraints
> should probably be listed in the "Compatibility" section.

<canofworms>

A quick reading of the SQL standard suggests to me that the way we are 
doing null handling in domain constraints is all wrong.  The standard 
says that domain constraints are only checked on values that are not 
null.  So both the handling of constraints using the CHECK syntax is 
nonstandard and the existence of explicit NOT NULL constraints is an 
extension.  The CREATE DOMAIN reference page already explains why all of 
this is a bad idea.  Do we want to document all of that further, or 
maybe we just want to rip out domain not-null constraints, or at least 
not add further syntax for it?

</canofworms>