I have a parent table (P) with two child (CC and CD):
CREATE TABLE P
(
A CHAR(3), B CHAR(2),
CONSTRAINT PK_P PRIMARY KEY(A, B)
)
CREATE TABLE CC
(
A CHAR(3), B CHAR(2),
C CHAR(1),
CONSTRAINT PK_CC PRIMARY KEY(A, B, C),
CONSTRAINT FK_CC_P FOREIGN KEY(A, B) REFERENCES P(A, B)
)
CREATE TABLE CD
(
A CHAR(3), B CHAR(2),
D CHAR(1),
CONSTRAINT PK_CD PRIMARY KEY(A, B, D),
CONSTRAINT FK_CD_P FOREIGN KEY(A, B) REFERENCES P(A, B)
)
I want a table (T) with optional references to CC and CD:
CREATE TABLE T
(
TId INT AUTO_INCREMENT PRIMARY KEY,
-- more fields
CurrentA CHAR(3), CurrentB CHAR(2),
CurrentC CHAR(1),
CurrentD CHAR(1),
CONSTRAINT FK_T_CurrentCC
FOREIGN KEY(CurrentA, CurrentB, CurrentC)
REFERENCES CC(A, B, C),
CONSTRAINT FK_T_CurrentCD
FOREIGN KEY(CurrentA, CurrentB, CurrentD)
REFERENCES CD(A, B, D)
)
So that if both records Current CC and Current CD are present, they must refer to the same parent P record.
This is the first option, but I intuitively think it may create some problems which I'm not really sure about.
I'm also considering a second option:
CREATE TABLE TAlternative
(
TId INT AUTO_INCREMENT PRIMARY KEY,
-- more fields
CurrentCCA CHAR(3), CurrentCCB CHAR(2), CurrentCCC CHAR(1),
CurrentCDA CHAR(3), CurrentCDB CHAR(2), CurrentCDD CHAR(1),
CONSTRAINT FK_TAlternative_CurrentCC
FOREIGN KEY(CurrentCCA, CurrentCCB, CurrentCCC)
REFERENCES CC(A, B, C),
CONSTRAINT FK_TAlternative_CurrentCD
FOREIGN KEY(CurrentCDA, CurrentCDB, CurrentCDD)
REFERENCES CD(A, B, D),
CONSTRAINT CHK_CurrentCC_CurrentDD_Same_P
CHECK
(
-- Parent not specified
(
(CurrentCCA IS NULL AND CurrentCDA IS NULL)
AND
(CurrentCCB IS NULL AND CurrentCDB IS NULL)
)
-- Same parent
OR
(
(CurrentCCA = CurrentCDA)
AND
(CurrentCCB = CurrentCDB)
)
)
)
Which would be better?
I want the T table to be another entity with optional references to CC and/or CD, not a table to optimize (see the --more fields comment). Think about CC and CD being two kind of parts of a machine P that cannot exist without P (for example CC defines the positions inside the machine, and CD defines other kind of zones inside the machine), and T being a final product positioned in a position and/or zone.