Re: How to ensure only one of two sets of optional information?

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 Fri Dec 19 2003 - 17:59:24 CST