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)
