CREATE TABLE FOUR
(
FourId INT NOT NULL
, FourNom VARCHAR(64) NOT NULL
, CONSTRAINT FOUR_PK PRIMARY KEY (FourId)
) ;
CREATE TABLE FOUR_CYCLE
(
FourId INT NOT NULL
, Cycleno INT NOT NULL
, CONSTRAINT FOUR_CYCLE_PK PRIMARY KEY (FourId, Cycleno)
, CONSTRAINT FOUR_CYCLE_FOUR_FK FOREIGN KEY (FourId)
REFERENCES FOUR (FourId)
ON DELETE CASCADE
) ;
CREATE TABLE GATEAU
(
GateauId INT NOT NULL
, GateauNom VARCHAR(64) NOT NULL
, FourId INT NOT NULL
, Cycleno INT NOT NULL
, CONSTRAINT GATEAU PRIMARY KEY (GateauId)
, CONSTRAINT GATEAU_FOUR_CYCLE_FK FOREIGN KEY (FourId, Cycleno)
REFERENCES FOUR_CYCLE (FourId, Cycleno)
) ;
INSERT INTO FOUR (FourId, FourNom) VALUES
(1, 'four truc')
, (2, 'four tout')
, (3, 'four chose')
;
INSERT INTO FOUR_CYCLE (FourId, Cycleno) VALUES
(1, 1)
, (1, 2)
, (1, 3)
, (1, 4)
, (2, 1)
, (2, 2)
, (2, 3)
, (2, 4)
, (3, 1)
, (3, 2)
, (3, 3)
, (3, 4)
;
INSERT INTO GATEAU (GateauId, GateauNom, FourId, CycleNo) VALUES
(1, 'éclair café', 1, 1)
, (2, 'baba', 1, 3)
, (3, 'tarte tatin', 1, 1)
, (4, 'mille-feuille', 1, 2)
, (5, 'saint-honoré', 3, 2)
, (6, 'baba', 2, 3)
;
SELECT * from FOUR ;
SELECT * from FOUR_CYCLE ;
SELECT * from GATEAU ;
Partager