Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Multiple parent design

Re: Multiple parent design

From: --CELKO-- <joe.celko_at_northface.edu>
Date: 17 Apr 2004 07:51:04 -0700
Message-ID: <a264e7ea.0404170651.4e38f00b@posting.google.com>


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 - 09:51:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US