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.
It's an intentional design decision actually; I had it creating multiple
constraints at first, but it had some ugly problems, so I made it behave
this way (which was no small amount of changes). I think the first time
I posted an implementation that worked this way was here
https://postgr.es/m/20230315224440.cz3brr6323fcrxs6@alvherre.pgsql
and then we debated it again later, starting at the bottom of
https://www.postgresql.org/message-id/flat/CAEZATCUA_iPo5kqUun4myghoZtgqbY3jx62%3DGwcYKRMmxFUq_g%40mail.gmail.com#482db1d21bcf8a4c3ef4fbee609810f4
A few messages later, I quoted the SQL standard for DROP NOT NULL, which
is pretty clear that if you run that command, then the column becomes
possibly nullable, which means that we'd have to drop all matching
constraints, or something.
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.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"People get annoyed when you try to debug them." (Larry Wall)