Re: Primary Key on Inherited Table - Mailing list pgsql-hackers

On Wed, 10 May 2000, Hannu Krosing wrote:
> Tom Lane wrote:
> > 
> > Wenjin Zheng <wenjin.zheng@lsbc.com> writes:
> > > Is there a way to specify primary key in the child table using columns
> > > from parent table?  I am using 6.5.0.
> 
> IMHO primary key itself should be one of inherited "attributes", and 
> it should be unique over all parents and children.
> 
> I think that use of inheritance as it is in current PostgreSQL should 
> be discouraged.
> 
> There has been some discussion about making PostgreSQL a real ORDBMS
> but as I understood the work was pushed back to wait for 7.0.
> 

I also ran into this problem of not being able to use an inherited attrib
as a primary key.  I sent out some email (by accident when I wasn't really even
finished with it (strange)!) about the distinction between inheriting and
extending a class.  Inherit is a very general term.  I have broken it down into
CLONES, EXTENDS, and ASSIMILATES (please consider):

EXTENDS is the case where a child shares the same primary key as one of its
parents.  The parent and child use the same storage of
common attributes and the child table really only stores the unique information
with row references back to its parent(s) to get the other attribs.  EXTENDS
creates a connected tree where the parent can't be dropped because its dependent
children are connected to it.  The child IS the parent(s), just concatenated
together and with some (possibly) new attributes.  In a way, this might be like
a special VIEW that also has its own new attributes.

In INHERITS (CLONES), the child just gets a COPY of the parent(s) structure with
options to specify inherited attribs in a primary key.  The child's primary key
values, like all its other attribs, are not shared with the parent. The child
could use the same sequence as a parent from which to get unique id values.  The
child would be independent of the parent after being created so that the parent
could be dropped without any problem.  This is like a new tree or cloning a
tree.  The parent would store a reference to the children so that select * from
parent*; would work.  This seems to be about how the current implementation of
INHERITS works now except you can't drop the parent.  It will give you an error
if you try.  Instead of saying INHERITS, the word CLONES might be more clear.
The original is the master of its clones - it can see them.  But like real
cloning, the originals can die, leaving just the clones.

ASSIMILATES might be a combination of CLONES and EXTENDS, where
reference counts on attributes would be used to keep track of them.  When the
reference count on an attribute is 0, no parent or child uses it, and it can be
dropped.  A child could assimilate a parent, but the parent could still be
dropped.  The child would continue to keep a reference on the attributes of the
parent.  Only data rows used by just the parent alone would get deleted from the
attributes when the parent is dropped.  You might say something like CREATE
TABLE child () ASSIMILATES(parent); This method allows the ability to exclude
some of the parents attributes from being inherited by the child.  SELECT
attrib1, attrib2, ... from child*; would output all of the selected
attributes values, not just the instances it contributed to the columns.  Of
course, when doing the select above, it would have to output entire rows. The
system would have to find all classes that use the same rows together and make a
union.  The storage of the attributes might involve maintaining
sections for each class that uses it. Actually, all classes that share
attributes using this method form a collective, not really a parent/child
relationship.  For this to work, the attributes have to be more
object-oriented, independent, and external to the classes that actually use
them.  So, when creating a table, you are in the process of creating new
attribute objects that other new classes can assimilate.

Here are some examples of cases using all three of these:

Ex. 1 CLONES
I want to create a child based on parent.  All attributes are inherited.  Parent
should be able to see all the children and select common attributes recursively
through the children and grandchildren etc.  The parent should be able to die
and the children remain independent.
Solution: CREATE TABLE child (...) CLONES(parent);

Ex. 2 EXTENDS
I want to create a child based on parent.  I want to share the primary key and
attributes inherited from the parent with the parent.  All attributes are
inherited.  The child simply extends the parent with additional attributes that
make it more specialized.  The child will depend on the parent for storage of
attributes it inherits(uses).  When I insert into child, I want the parents
attributes inserted into the parent, and just the extended attributes stored in
the child.  The child can be dropped and the attributes it added to the parent
may persist or be deleted by option of child (the child may leave an
inheritance to the parent!).
Solution: CREATE TABLE child (...) EXTENDS(parent);

Ex. 3 ASSIMILATES
I want to create a child based on parent.  I might not want to inherit all
attributes of parent.  I don't care if the parent is later dropped - the child
shares the parents attributes with the other survivers.  I want to share
attributes storage with the parent (while it exists) and its other siblings,
that is, the parent see the data it has in common with the child.  The child
can select attributes in common with the parent and all its other siblings!  I
might want the child to share a primary key with the parent (which might also
be a primary key of other siblings) by choosing to inherit it.  I want the
child to join the parent (and its other siblings) as a collective. Solution: 
CREATE TABLE child (...) ASSIMILATES(parent);

All the methods above use inheritance of attributes from a base class, just in
different ways.  INHERIT may be too general a term to use in describing all the
possibilities.

Well, anyhow, I hope some of this will be considered when the inheritance
system in PostgreSQL is maybe redesigned.

-- 
Robert B. Easter
reaster@comptechnews.com