Re: How to ensure only one of two sets of optional information?
Date: 19 Dec 2003 15:59:24 -0800
Message-ID: <a264e7ea.0312191559.2c8329e1_at_posting.google.com>
CREATE TABLE Classes
(class_id INTEGER NOT NULL,
subclass_type CHAR(1) NOT NULL
CHECK(sub_class_type IN ('A', 'B')),
PRIMARY KEY (class_id, subclass_id),
... );
CREATE TABLE SubClass_A
(class_id INTEGER NOT NULL,
sub_class_type CHAR(1) DEFAULT 'A' NOT NULL
CHECK(sub_class_type = 'A'),
FOREIGN KEY (class_id, subclass_id)
REFERENCES Classes(class_id, subclass_id),
... );
CREATE TABLE SubClass_B
(class_id INTEGER NOT NULL,
sub_class_type CHAR(1) DEFAULT 'B' NOT NULL
CHECK(sub_class_type = 'B'),
FOREIGN KEY (class_id, subclass_id)
REFERENCES Classes(class_id, subclass_id),
... );
You can play games with inclusion rules in the CHECK() constriants, but this is the basic mutual exclusion pattern. Received on Sat Dec 20 2003 - 00:59:24 CET