Re: cataloguing NOT NULL constraints

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)