Inheritance, referential integrity and other constraints

From: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
To: Jan Wieck <wieck(at)debis(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Inheritance, referential integrity and other constraints
Date: 2000-01-24 21:52:56
Message-ID: 200001242152.VAA25637@linda.lfix.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I would like to work on improving implementation of inheritance,
especially with regard to referential integrity. I suspect there are
a number of issues that may be related and will need to be done together.
In addition, this will be my first attempt to do anything serious in
the PostgreSQL code itself, so I would like to get some hints as
to what I haven't even thought about!

First, I would like to change the definition of the foreign key
constraints to allow the inheritance star to follow a table name.
This would mean that, for RI purposes, the named table would be
aggregated with its descendants. So "REFERENCES tbl" would mean that
the foreign key must exist in tbl, but "REFERENCES tbl*" would allow it
to exist either in tbl or in any of tbl's descendants.

Implications: where * is used, dropping a descendant table is OK, so
long as the parent continues to exist. ON DELETE actions would apply
to all the relations in the table to be dropped; to reduce complexity,
this should be broken down into:
`DELETE FROM descendant; DROP TABLE descendant'
and the whole should be treated as atomic. If any one relation could
not be deleted, the whole operation would fail.

Use of ON DELETE or ON UPDATE implies there must be an index on the
referring column, to enable checking or deletion to be done speedily.
This doesn't seem to happen at the moment. If the reference is to
an inheritance group, it would seem to be appropriate that all the
tables in the group should use the same index. Similarly, where
a unique or primary key constraint is inherited, it may be desirable
to use a single index to manage the constraint. The implication of
this would be that there must be a check when a table is dropped
to make sure that a grouped index is not dropped until the last
table in the group is dropped.

Is this feasible, or would it require too many changes elsewhere?

Another item I would like to get fixed is to make sure that all
constraints are inherited when a descendant table is created; this
is a current TODO item. It will also be necessary to ensure that
added constraints get inherited, when ALTER TABLE ... ADD/DROP
CONSTRAINT gets implemented.

====== Design proposal =======

I think that the implications of inheritance have never been fully
explored and I would like to establish the framework in which future
work that involves inheritance will be done.

It seems to me that declaring a table to inherit from another, and
enabling both to be read together by the table* syntax, together
imply certain things about an inheritance group:

1. All tables in the group must possess all the columns of their
ancestor, and all those columns must be of the same type.

2. Some constraints at least must be shared - primary key is the most
obvious example; I think that _all_ constraints on inherited columns
should be shared. It is probably not practicable to force table
constraints to be shared upwards.

3. There seems to be no need to enforce similar restrictions on
GRANT. In fact it is quite likely that different permissions could
apply to different tables in the hierarchy.

4. Dropping a table implies dropping all its descendants.

==============================

I would like to consider the implications of this proposal in the light
of the ALTER TABLE commands that have recently been added.

The grammar for ALTER TABLE allows either `ALTER TABLE table ...' or
`ALTER TABLE table* ...'. I would like to suggest that an alteration
to a parent table must necessarily involve all its descendants and
that alterations to inherited columns must be done in the appropriate
parent. So, given this hierarchy of tables:

t1 (c1 char(2) primary key,
c2 text)

t2 (c3 int not null
c4 timestamp default current_timestamp) inherits (t1)

t3 (c5 text not null) inherits (t2)

adding a column to t1, means the same column must be added to t2 and t3
and must appear before any columns originating in t2; columns c1 to c4
cannot be dropped from table t3 unless they are also dropped from the
parents. Alterations to c2 must be done in t1, and alterations to c4
must be done in t2. Any table constraint applied to t1 would automatically
be inherited by t2 and t3, a new constraint added to t2 would be
inherited by t3 but would not affect t1.

Attempts to use ALTER TABLE to bypass these restrictions should be
disallowed.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"If anyone has material possessions and sees his
brother in need but has no pity on him, how can the
love of God be in him?"
I John 3:17

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2000-01-24 22:18:07 Re: [HACKERS] Well, then you keep your darn columns
Previous Message Hannu Krosing 2000-01-24 21:52:07 Re: [HACKERS] Well, then you keep your darn columns