1

Notes on tables A, B, C, D, E

  • B.aid and C.aid reference A.aid.
  • D.cid references C.cid and D.did is the primary key. D is a C.
  • cid and did are pre-assigned and I cannot change them (they need to remain separate despite the is-a).
  • E has information that depends on its primary key (bid, did) with bid referencing B.bid and did referencing D.did.

Is there a way to make sure that the bid and did of a given row in E reference rows in B and D that reference the same row in A (through C in the case of D)?

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?

  • Yes, there is. Add aid to all tables and modify FK and UNIQUE constraints accordingly – ypercubeᵀᴹ May 25 at 18:24
  • Oh, I see you came up with something like that. It needs correction though in some points. – ypercubeᵀᴹ May 25 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.