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 29 30 31 32 33 34 35
| CREATE TABLE T_PERSONNES (
PER_ID INTEGER NOT NULL,
PER_NOM VARCHAR (32) NOT NULL,
PER_PRENOM VARCHAR (24) NOT NULL,
PER_DATE_NAI DATE NOT NULL,
PER_SS_SE CHAR (1), -- sexe
PER_SS_AN CHAR (2), -- année
PER_SS_MO CHAR (2), -- mois
PER_SS_CO CHAR (5), -- N° commune
PER_SS_RA CHAR (3), -- ordre d'enregistrement
PER_SS_CL CHAR (2), -- clé
CONSTRAINT PK_PER_ID PRIMARY KEY (PER_ID),
CONSTRAINT CC_PER_SS_SE CHECK (PER_SS_SE IS NULL OR CAST (PER_SS_SE AS INTEGER) IN ('1', '2', '3', '4', '7', '8'),
CONSTRAINT CC_PER_SS_AN CHECK (PER_SS_AN IS NULL OR CAST (PER_SS_AN AS INTEGER) BETWEEN '00' AND '99'),
CONSTRAINT CC_PER_SS_MO CHECK (PER_SS_MO IS NULL OR CAST (PER_SS_MO AS INTEGER) BETWEEN '01' AND '12'),
CONSTRAINT CC_PER_SS_CO CHECK (PER_SS_CO IS NULL OR CAST (PER_SS_CO AS INTEGER) BETWEEN '01001' AND '99999'),
CONSTRAINT CC_PER_SS_RA CHECK (PER_SS_RA IS NULL OR CAST (PER_SS_RA AS INTEGER) BETWEEN '000' AND '999'),
CONSTRAINT CC_PER_SS_CL CHECK (PER_SS_CL IS NULL OR CAST (PER_SS_CL AS INTEGER) BETWEEN '00' AND '97'),
CONSTRAINT CC_PER_SS CHECK (
(PER_SS_SE IS NULL
AND PER_SS_AN IS NULL
AND PER_SS_MO IS NULL
AND PER_SS_CO IS NULL
AND PER_SS_RA IS NULL
AND PER_SS_CL IS NULL)
OR
(PER_SS_SE IS NOT NULL
AND PER_SS_AN IS NOT NULL
AND PER_SS_MO IS NOT NULL
AND PER_SS_CO IS NOT NULL
AND PER_SS_RA IS NOT NULL
AND PER_SS_CL IS NOT NULL)
),
CONSTRAINT UC_PER_SS UNIQUE (PER_SS_SE, PER_SS_AN, PER_SS_MO, PER_SS_CO, PER_SS_RA, PER_SS_CL)
); |
Partager