On 2023-Jul-24, Dean Rasheed wrote: > Hmm, I'm not so sure. I think perhaps multiple NOT NULL constraints on > the same column should just be allowed, otherwise things might get > confusing. For example: > create table p1 (a int not null check (a > 0)); create table p2 (a int not null check (a > 0)); create table foo () inherits (p1, p2);
Have a look at the conislocal / coninhcount values. These should reflect the fact that the constraint has multiple sources; and the constraint does disappear if you drop it from both sources. > If I then drop the p1 constraints: > > alter table p1 drop constraint p1_a_check; > alter table p1 drop constraint p1_a_not_null; > > I end up with column "a" still being not null, and the "p1_a_not_null" > constraint still being there on foo, which seems even more > counter-intuitive, because I just dropped that constraint, and it > really should now be the "p2_a_not_null" constraint that makes "a" not > null: I can see that it might make sense to not inherit the constraint name in some cases. Perhaps: 1. never inherit a name. Each table has its own constraint name always 2. only inherit if there's a single parent 3. always inherit the name from the first parent (current implementation) > So I'd say that ALTER TABLE ... ADD NOT NULL should always add a > constraint, even if there already is one. For example ALTER TABLE ... > ADD UNIQUE does nothing to prevent multiple unique constraints on the > same column(s). It seems pretty dumb, but maybe there is a reason to > allow it, and it doesn't feel like we should be second-guessing what > the user wants. That was my initial implementation but I changed it to allowing a single constraint because of the way the standard describes SET NOT NULL; specifically, 11.15 <set column not null clause> says that "If the column descriptor of C does not contain an indication that C is defined as NOT NULL, then:" a constraint is added; otherwise (i.e., such an indication does exist), nothing happens. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "La virtud es el justo medio entre dos defectos" (Aristóteles)
