Re: what about uniqueness of inherited primary keys

On Sun, 2003-12-28 at 20:17, Andreas wrote:
> what about uniqueness of inherited primary keys ?
...
> Shouldn't we expect to have unique entries in a primary key collumn ?

We should, but it doesn't happen.  This is a very long-standing defect
which has not yet been solved.  To solve it would need an index attached
to multiple tables and the mechanism for that does not yet exist.

The same applies to foreign keys; these too are not inherited.

The solution for primary keys is something like this:

CREATE TABLE tree_index (tboid oid, id varchar(10),primary key
(id),unique (tboid, id));

CREATE TABLE person (id varchar(10) primary key, surname text,foreign
key (tableoid, id) references tree_index(tboid, id) match full on update
cascade on delete cascade);

CREATE TABLE parent (eldest_child varchar(10), primary key (id),foreign
key (tableoid, id) references tree_index(tboid, id) match full on update
cascade on delete cascade) inherits (person);

CREATE TABLE child (father varchar(10) references parent(id), mother
varchar(10) references parent(id), primary key (id), foreign key
(tableoid, id) references tree_index(tboid, id) match full on update
cascade on delete cascade) inherits (person);

alter table parent add constraint "child key" foreign key (eldest_child)
references child (id) on update cascade on delete restrict;

Then you need a trigger on each table to create a record in tree_index
when a new record is added to a table in the hierarchy.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "For God shall bring every work into judgment,
      with every secret thing, whether it be good, or
      whether it be evil."               Ecclesiastes 12:14