Re: cataloguing NOT NULL constraints

On tor, 2011-08-04 at 16:15 -0400, Alvaro Herrera wrote:
> > Have you considered just cataloging NOT NULL constraints as CHECK
> > constraints and teaching the reverse parser to convert "x CHECK (x IS
> > NOT NULL)" to "x NOT NULL".
>
> Hmm, no, I admit I haven't. The current approach was suggested very
> early in the history of this patch.

Well, the early patch thought this was a small problem. Now we know
it's a big problem, so it might be better to solve it terms of another
big problem that is already solved. :-)

> > It seems to me that we're adding a whole
> > lot of hoopla here that is essentially identical to the existing CHECK
> > constraint support (it must be, per SQL standard), for no additional
> > functionality.
>
> Yeah, perhaps you're right. The main reason they were considered
> separately is that we wanted to have them to be optimized via
> pg_attribute.attnotnull, but my patch does away with the need for that
> because it is maintained separately anyway.

Hmm, OK, but in any case you could have kept attnotnull and treated it
as a kind of optimization that indicates whether you can derive
not-nullability from existing CHECK constraints (which you can easily do
in enough cases).

> Before embarking on rewriting this patch from scratch, I would like to
> know what's your opinion (or the SQL standard's) on the fact that this
> patch separated the PRIMARY KEY from NOT NULL constraints, so that they
> don't act exactly alike (to wit, the not-nullness of a PK does not
> inherit while the one from a NOT NULL constraint does).

The SQL standard deals with inheritance in terms of composite types,
which don't have constraints, so that doesn't give any guidance.

That said, I think the substitutability property of object-oriented
systems, namely that you can use a child object in place of a parent
object, requires in principle that we inherit all constraints (by
default, at least). We don't inherit primary keys because of
implementation issues with indexes, but at some point in the future we
should fix that. So to some degree, inheriting the not-null property of
primary keys while not inheriting the rest of it is a bit wrong, but it
would appear to be a step in the right direction, and changing
established behavior seems a bit gratuitous to me.