1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| CREATE TABLE US
(
usId VARCHAR(4),
CONSTRAINT US_PK PRIMARY KEY(usId)
);
CREATE TABLE SE
(
usId VARCHAR(4),
seId VARCHAR(4),
CONSTRAINT SE_PK PRIMARY KEY(usId, seId),
CONSTRAINT SE_US_FK FOREIGN KEY(usId) REFERENCES US(usId)
);
CREATE TABLE WO
(
woId VARCHAR(4),
usId_US VARCHAR(4),
usId_SE VARCHAR(4),
seId VARCHAR(4),
CONSTRAINT WO_PK PRIMARY KEY(woId),
CONSTRAINT WO_US_FK FOREIGN KEY(usId_US) REFERENCES US(usId),
CONSTRAINT WO_SE_FK FOREIGN KEY(usId_SE, seId) REFERENCES SE(usId, seId)
, CONSTRAINT WO_PARTOCHE_CHK CHECK (usId_US IS NOT NULL
AND usId_SE IS NULL AND seId IS NULL
OR usId_US IS NULL
AND usId_SE IS NOT NULL AND seId IS NOT NULL)
); |
Partager