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.
