Bonjour.

J'essaie de créer des contraintes pour un numéro de sécurité sociale mais c'est incorrect :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
36
37
38
39
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_DE CHAR (2),	-- département
	PER_SS_CO CHAR (3),	-- 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) BETWEEN '1' AND '2'),
	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_DE CHECK ((PER_SS_DE IS NULL OR CAST (PER_SS_DE AS INTEGER) BETWEEN '01' AND '96') OR PER_SS_DE = '2A' OR PER_SS_DE = '2B'),
	CONSTRAINT CC_PER_SS_CO CHECK (PER_SS_CO IS NULL OR CAST (PER_SS_CO AS INTEGER) BETWEEN '000' AND '999'),
	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_DE 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_DE 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_DE, PER_SS_CO, PER_SS_RA, PER_SS_CL)
);
Par exemple, pour le mois de naissance, un 13 n'est pas validé mais un 1 l'est avec ou sans 0 devant.

Merci.