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>