Re: cataloguing NOT NULL constraints

On 6 August 2011 01:01, Peter Eisentraut <peter_e@gmx.net> wrote:
>> 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.
>

The current behaviour is inconsistent - the not-null property of a PK
is sometimes inherited and sometimes not, depending on whether the PK
is added at table-creation time or later. So a change in either
direction is a change to some current behaviour, unless we leave it
inconsistent, which seems unacceptable.

So I don't think compatibility arguments apply here, and I would tend
to agree that inheriting the not-null property of PKs while not
inheriting the rest seems wrong.

Regards,
Dean