On 12.02.24 11:24, Alvaro Herrera wrote:
> On 2024-Feb-11, Peter Eisentraut wrote:
>> But I see that table constraints do not work that way. A command like ALTER
>> TABLE t1 ADD NOT NULL c1 does nothing if the column already has a NOT NULL
>> constraint. I'm not sure this is correct. At least it's not documented.
>> We should probably make the domains feature work the same way, but I would
>> like to understand why it works that way first.
> The main source of nastiness, when we allow multiple constraints, is
> constraint inheritance. If we allow just one constraint per column,
> then it's always easy to know what to do on inheritance attach and
> detach: just coninhcount+1 or coninhcount-1 of the one relevant
> constraint (which can be matched by column name). If we have multiple
> ones, we have to know which one(s) to match and how (by constraint
> name?); if the parent has two and the child has one, we need to create
> another in the child, with its own coninhcount adjustments; if the
> parent has one named parent_col_not_null and the child also has
> child_col_not_null, then at ADD INHERIT do we match these ignoring the
> differing name, or do we rename the one on child so that we now have
> two? Also, the clutter in psql/pg_dump becomes worse.
>
> I would suggest that domain not-null constraints should also allow just
> one per column.
Perhaps it would make sense if we change the ALTER TABLE command to be like
ALTER TABLE t1 ADD IF NOT EXISTS NOT NULL c1
Then the behavior is like one would expect.
For ALTER TABLE, we would reject this command if IF NOT EXISTS is not
specified. (Since this is mainly for pg_dump, it doesn't really matter
for usability.) For ALTER DOMAIN, we could accept both variants.