Re: Some thoughts on table inheritance (which is uniquely awesome on PostgreSQL) - Mailing list pgsql-general

On Aug 22, 2012, at 23:22, Chris Travers <chris.travers@gmail.com> wrote:

>  * unique constraints being able to be marked INHERIT or NOINHERIT.
> A unique constraint that is marked INHERIT would be automatically
> created again on the child table.  This could be documented to be
> domain-specific to each child table, and that if you need super-set
> unique constraints, you need to borrow techniques from table
> partitioning.
>
>
> * PRIMARY KEY inheritance would be documented as necessarily
> excluded by multiple inheritance.  The concept simply doesn't make
> sense when a child table can have multiple parents.  If it did, you'd
> have multiple primary keys.  According to Oracle and DB2
> documentation, the best they offer regarding such things is an OID
> field anyway.....
>

late night thinking here...

An inherited PK constraint could be made into a unique/not-null constraint if a second PK constraint becomes inherited.
In that case the table no longer has a PK constraint until the user creates one that makes sense. 

This whole issue occurs due to surrogate keys being used as PK.  In a partitioning scheme the partitioning field should
bepart of the natural key and thus cross-relation matching could not occur in the absence of a mis-allocation which a
partitionspecific check constraint on that column would solve. 

In an OO situation, in the absence of partitioning, a key is a concept of identity.  Identity requires that the type of
twoentities matches; and the type of a child object will never match the type of an object of its parent.  Thus
regardlessof single or multiple inheritance PK inheritance makes no sense in an OO situation. 

Even with multiple inheritance you might want to inherit a PK from from parent but from the other parent(s) you might
simplywant to inherit their PK as a unique constraint.  In so doing you assert that you are on the same level as the PK
parentobjects while you have different attributes than your siblings.  Jack and Jill can both inherit PK from human
beingbut could inherit phone_number and email from contact_info (not the best example I know...I tried making gender
workhere but my mind went blank if trying to rationalize why gender wouldn't just be a FK). 

For FK, however, the question is whether I am referencing a specific instance or whether I simply am referencing an
arbitraryset of properties that anything matching those properties could match.  Currently the former is what we have,
andsince we are dealing with entities (as opposed to behavior) that makes sense.  Ignoring partitioning if I define an
FKrelationship to "flying things"  I supposedly do not care whether you store a bird-type flyer or an airplane-type
flyer. If someone names their pet bird Polly and someone else names a plane Polly then what...Inheriting an FK to a
targetnon-partitioned table makes sense but how does one deal with inheriting onto a target table that has children? 

Just some thoughts as I have not, as became obvious writing this, thought through using the database in this fashion.
Mostinheritance I have used is behavioral in nature whereas a database deals with identity.  Segregating between type
compositionand partitioning mentally, and ideally in the language, makes a lot of sense to me.  It seems that currently
bothmodels are partially implemented and done so using the same syntactical foundation... 

David J.