Notes on tables A, B, C, D, E
D.didis the primary key.
didare pre-assigned and I cannot change them (they need to remain separate despite the is-a).
Ehas information that depends on its primary key (
Is there a way to make sure that the
did of a given row in
E reference rows in
D that reference the same row in
C in the case of
So far I've come up with
CREATE TABLE a ( aid INTEGER PRIMARY KEY ); CREATE TABLE b ( bid INTEGER PRIMARY KEY, aid INTEGER NOT NULL REFERENCES a ( aid ), UNIQUE ( bid, aid ) ); CREATE TABLE c ( cid INTEGER PRIMARY KEY, aid INTEGER NOT NULL REFERENCES a ( aid ), UNIQUE ( cid, aid ) ); CREATE TABLE d ( did INTEGER PRIMARY KEY, cid INTEGER NOT NULL REFERENCES c ( cid ), UNIQUE ( did, cid ) ); CREATE TABLE e ( bid INTEGER NOT NULL REFERENCES b ( bid ), cid INTEGER NOT NULL REFERENCES c ( cid ), did INTEGER NOT NULL REFERENCES d ( did ), dcid INTEGER NOT NULL, baid INTEGER NOT NULL, caid INTEGER NOT NULL, PRIMARY KEY ( bid, did ), FOREIGN KEY ( bid, baid ) REFERENCES b ( bid, aid ), FOREIGN KEY ( cid, caid ) REFERENCES c ( cid, aid ), FOREIGN KEY ( did, dcid ) REFERENCES d ( did, cid ), CHECK ( baid = caid AND cid = dcid ) );
Is there a better way to create
E that doesn't seem so complicated and redundant?