Re: Multiple parent design

Is this close to what you want?

CREATE TABLE Parent1
(parent_id INTEGER NOT NULL,

 parent_type INTEGER DEFAULT 1 NOT NULL

     CHECK(parent_type = 1),
 PRIMARY KEY (parent_id, parent_type)
 FOREIGN KEY (parent_id, parent_type)
 REFERENCES Children (parent_id, parent_type),  ..);

CREATE TABLE Parent2
(parent_id INTEGER NOT NULL,

 parent_type INTEGER DEFAULT 2 NOT NULL

     CHECK(parent_type = 2),
 PRIMARY KEY (parent_id, parent_type),
 FOREIGN KEY (parent_id, parent_type)
 REFERENCES Children (parent_id, parent_type),  ..);

Etc.

CREATE TABLE Children
(child_id INTEGER NOT NULL PRIMARY KEY,
 parent_id INTEGER NOT NULL,
 parent_type INTEGER NOT NULL

     CHECK(parent_type IN (1,2, ..)),
 UNIQUE(parent_id, parent_type),
 ..);

This requires that each child has one and only one parent in one and only one parent table. No child can be deleted while he has a parent. Received on Sat Apr 17 2004 - 16:51:04 CEST