inheritance and primary and foreign keys - Mailing list pgsql-hackers

From Robert B. Easter
Subject inheritance and primary and foreign keys
Date
Msg-id 00050902561401.20915@comptechnews
Whole thread Raw
List pgsql-hackers
I'm new to RDBMS so what follows is probably old crusty questions but
might have some relation to the new foreign key implementation:

In database table inheritance, which may be different than inheritance as it is
in programming languages, shouldn't derived classes only store the additional
attribributes?

Shouldn't the inherited attributes be obtained by reference(s) to a row(s) in
the base class(es)?

Shouldn't when a derived class inherits a base class(es), that it
optionally inherit a user selected PRIMARY KEY of one of its base classes as its
PRIMARY KEY.  The other PRIMARY KEYs of the base classes (rejected candidates),
are inherited as is like the rest of the other inherited attributes and
with their constraints intact?

I could imagine a syntax like:
CREATE TABLE child (... unique attributes of a child) INHERITS(parent1,
parent2) WITH PRIMARY KEY;

The WITH PRIMARY KEY, means that child takes the PRIMARY KEY of the first
base class listed in INHERITS() as its own PRIMARY KEY.  If WITH PRIMARY
KEY is omitted, then the child is free to make one of its own unique attributes
its PRIMARY KEY.

This is totally different than how things are working and I'm probably wrong!

Under this scheme, an INSERT INTO child, results in an INSERT INTO each of its
base classes using those attributes it passed on to the child.  A row in the
child class would really only contain storage of the unique attributes it adds
to the bases AND record OIDs for each base class that each point to a row
containing its attributes for the child instance. The record OIDS would be in
the order as they were in the INHERITS clause if that would matter.

What I describe is more like a TREE since the derived and base classes remain
tightly linked.  In inheritance like in programming languages, or in real life,
attributes are just copied and the new entity has no linkage to its parents
anymore - it can be killed independent of its parent.  I've noticed that in
Postgres, this is not the case.  You have to DROP the derived classes before
you can DROP the base classes.  Since there is already the some linkage, this
is not really inheritance in Postgres.  A derived class here is like adding
limbs to a tree that gets it more specialized rather than totally different and
disconnected.  To cut the tree down, you trim its limbs off first a little or
just plunge it all down.

A fully implemented tree-like inheritance allows the derived classes to act
as connected extensions of the base and optionally share/use an existing PRIMARY
KEY.  I like the word EXTENDS instead of INHERITS for this.  There is a
difference.  EXTENDS is like what I am talking about, a tree structure that
builds up and remains connected.  INHERITS to me means spawning off a new trunk
that initially has at least all of the properties of an existing tree - no
linkage back to the parent except to say it is of that class and some more. 
The class resulting from being EXTENDS from from a parent remains connected to
the parent branch and shares its attributes.  A class that INHERITS a base
class should be independent of the base.  The base should be DROPpable
after the derived class has been CREATE?


-- 
Robert B. Easter
reaster@comptechnews.com


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Commercializing PostgreSQL: What do you thing?
Next
From: The Hermit Hacker
Date:
Subject: She's out the door ...