Re: not null constraints, again

On Tue, Sep 17, 2024 at 1:47 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>

still digging inheritance related issues.

drop table if exists pp1,cc1, cc2;
create table pp1 (f1 int, constraint nn check (f1 > 1));
create table cc1 (f2 text, f3 int ) inherits (pp1);
create table cc2(f4 float, constraint nn check (f1 > 1)) inherits(pp1,cc1);
execute constr_meta('{pp1,cc1, cc2}');
alter table only cc2 drop constraint nn;
ERROR: cannot drop inherited constraint "nn" of relation "cc2"

So:

drop table if exists pp1,cc1, cc2;
create table pp1 (f1 int not null);
create table cc1 (f2 text, f3 int not null no inherit) inherits (pp1);
create table cc2(f4 float, f1 int not null) inherits(pp1,cc1);
execute constr_meta('{pp1,cc1, cc2}');
alter table only cc2 drop constraint cc2_f1_not_null;

Last "alter table only cc2" should fail?
because it violates catalog-pg-constraint coninhcount description:
"The number of direct inheritance ancestors this constraint has. A
constraint with a nonzero number of ancestors cannot be dropped nor
renamed."

also
alter table only cc2 drop constraint cc2_f1_not_null;
success executed.
some pg_constraint attribute info may change.
but constraint cc2_f1_not_null still exists.