Re: [HACKERS] inheritance and primary and foreign keys

On Tue, 09 May 2000, Robert B. Easter wrote:
> I'm new to RDBMS so what follows is probably old crusty questions but
> might have some relation to the new foreign key implementation:

[snip]

I didn't mean to send this email. It was just a draft that I was playing with!
There are probaby very good reasons why INHERITS works the way it does.
Sorry.

Anyway, I like the idea of a separate treatement of INHERITS and EXTENDS for
implementing object hierarchy.  For INHERIT, a child table just gets a
COPY of the structure of the parent with maybe some possibility to take
inherited attribute(s) and/or new attributes as a primary key.  The parent can
be dropped since the child is independent of its parent.  The parent would
'know' about its children, but the child need not know about the parent.
SELECT * from parent*; would work.

In EXTENDS, parent and child are connected branches that share attributes and
storage so that you can't drop the parent unless you drop dependent children
first.  The children would again, have an ability to use inherited attribs as a
primary key, possibily in combination with new attributes.  The default
might be that if the parent has a primary key, it will also be the
primary key of the child.  The child and parent would be using the SAME key
with indexes etc shared. Inserting into the child results in insertion into the
parent branch of the inherited attribs and an insert into the child of just its
unique attribs.  The child just extends its parent with more attributes.
SELECT * from parent*; would act just like it does in INHERITS.

Again, INHERITS is like starting a new independent trunk that initially gets the
structure of what it inherits.  EXTENDS adds a branch to a connected tree
structure to create more specialization.

Anyhow, this kind of topic probably bores people and has been beaten into the
ground in the past.  If there is an archive about topics like this in
PostgreSQL hackers, I'd like to know the time periods and I'd go read it.

I am able to get the system to act almost the way I want by doing  this:

CREATE TABLE employee (
    em_id        SERIAL PRIMARY KEY,
    name        TEXT,
    ...
);

CREATE TABLE manager (
    ...
) INHERITS(employee);

The SERIAL type gets inherited with its modifier to use the same sequence as
employee does.  Only thing missing is that em_id is not the PRIMARY KEY in
manager and I don't see how to do it.

--
Robert B. Easter
reaster@comptechnews.com