Re: BUG #5064: not-null constraints is not inherited

"Tomonari Katsumata" <katsumata(dot)tomonari(at)oss(dot)ntt(dot)co(dot)jp> writes:
> postgres=# CREATE TABLE parent (id integer);
> postgres=# CREATE TABLE child () inherits (parent);
> postgres=# ALTER TABLE parent ADD PRIMARY KEY (id);

> child table does not have a "not null" constraints.
> I think it's not desirable behavier.

The code says:

* XXX: Shouldn't the ALTER TABLE .. SET NOT NULL cascade to child
* tables? Currently, since the PRIMARY KEY itself doesn't cascade,
* we don't cascade the notnull constraint(s) either; but this is
* pretty debatable.

Given that we've tightened things up to insist that child tables have
all the check constraints that the parent does, it seems like it might
be desirable to make the same rule for NOT NULL constraints. However,
while changing DefineIndex for this would be a one-liner change, there
are a lot of other implications of such a rule. This would have to
be rejected:
alter table only parent alter id set not null;
which again isn't a big deal. What is a big deal is that we'd also
have to forbid ALTER DROP NOT NULL on the child. For that, we'd need
to track whether NOT NULL constraints were inherited or not. There
is already a TODO item about that. I've added this thread to the
TODO entry.

regards, tom lane