Re: super type & sub type, interested too

From: Lennart Jonsson <lelle2_at_bonetmail.com>
Date: Wed, 06 Nov 2002 21:43:30 +0100
Message-ID: <aqburm$8i04k$1@ID-167942.news.dfncis.de>

On Wed, 06 Nov 2002 18:26:25 +0100, Michael Gast wrote:

[...]
>
> I assume you are using the doc_id in both, the supertype and your
> subtype tables. In addition i assume you have foreign keys from each
> subtype to your supertype.
>
> This given:
> 1. Insert your row in the supertype table.
> 2. Insert your row in the subtype table.
>
> Additional hint: If you have distinct subtypes you should add insert and
> update triggers on each subtype table to ensure that a primary key value
> you want to insert in one subtype table is not used in another subtype
> table.

I assume the discussion is regarding normal parent and child tables (and not some special structured types or similar). I.e. something like (pls ignore typos, to long identifiers etc):

• super table create table shipping_document ( doc_id integer not null, doc_type_id integer not null, shp_id integer, constraint pk_ship_doc primary key (doc_id), constraint valid_doc_type check (doc_type_id in (1,2,...)) )
• sub table1 create table house_bill_of_lading ( doc_id integer not null, info char(100), constraint pk_house_bill primary key (doc_id), constraint fk1_house_bill foreign key(doc_id) references shipping_document )
• sub table2 ...

One way of validating super/sub type is as you point out via triggers. Another one which I have been thinking about would be to add a unique constraint in the super table, and use that as foreign key in the subtable. Together with a check constraint in the subtable that should also do the trick. I.e.:

create table shipping_document (

    doc_id integer not null,
    doc_type_id integer not null,
    shp_id integer,

    constraint pk_ship_doc primary key (doc_id),     constraint valid_doc_type check (doc_type_id in (1,2,...)),     constraint ak_ship_doc unique (doc_id, doc_type_id) );

• sub table1 create table house_bill_of_lading ( doc_id integer not null, doc_type_id integer not null, info char(100), constraint pk_house_bill primary key (doc_id), constraint fk1_house_bill foreign key(doc_id, doc_type_id) references shipping_document (doc_id, doc_type_id), constraint correct_doc_type check (doc_type_id = 1) );
• sub table2 ...

Any thoughts on benefits/drawbacks with these two methods respectively?

/Lennart Received on Wed Nov 06 2002 - 14:43:30 CST