Contraintes de validation d'un numéro de sécurité sociale

**Nerva** · 17/05/2025, 09h24

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.

**Séb.** · 17/05/2025, 10h39

...
CONSTRAINT CC_PER_SS_AN CHECK (PER_SS_AN IS NULL OR CAST (PER_SS_AN AS INTEGER) BETWEEN '00' AND '99'),
...

Tu compares le résultat d'une conversion vers un entier à du texte. Cela fausse l'attendu.

C'est comme si tu faisais :

select 5 between '00' and '99'; -- TRUE

S'agissant de texte je ferais :

constraint year_is_2digit check (per_ss_an regexp '^[0-9]{2}$'),

(MySQL)

**escartefigue** · 17/05/2025, 10h48

Bonjour,

Quelques remarques :

comme le NIR (anciennement NNI) - c'est à dire le n° de sécurité sociale - est un code régi par un organisme externe, il devrait être stocké en tant que tel dans une colonne unique et non pas éclaté dans plusieurs colonnes. Ce faisant, le codage de la contrainte sera plus simple.
dans certains systèmes d'information, le NIR n'est pas unique. Deux cas de figure : les enfants qui n'ont encore jamais travaillé et qui héritent du NIR du père ou de la mère et les NIR non certifiés, qui sont douteux.
il existe des NIR particuliers (CAFAT, MONACO, Lunaire...) qui ne répondent pas aux règles de contrôle d'un NIR national. Il ne faut donc pas mettre en place des contrôles de validité tels que vous les avez implémenté si dans vos individus, certains sont nés dans les TOM, à Monaco, au Maghreb...

**Nerva** · 17/05/2025, 11h31

@seb. Le problème serait le même avec un simple code postal. Que je contraigne ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONSTRAINT CC_LOC_CP CHECK (CAST(LOC_CP AS INTEGER) BETWEEN 01000 AND 97680)

Ou comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONSTRAINT CC_LOC_CP CHECK (CAST(LOC_CP AS INTEGER) BETWEEN '01000' AND '97680')

Je peux omettre le 0 dans les 9 premiers départements et c'est validé quand même. Mais bon, bien que je n'aie encore jamais utilisé d'expressions régulières dans des contraintes, si c'est la seule manière d'obliger la saisie du nombre exact de caractères définis dans un CHAR, je vais m'y mettre.

@escartefigue. Là encore j'avais trouvé un article il y a longtemps sur la nécessité d'éclater le numéro afin de faciliter la conception des requêtes pour des recherches précises d'individus. Mais si il existe une contrainte plus simple pour un numéro défini dans une seule colonne, je suis preneur.

À la base j'avais pensé à ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONSTRAINT CC_SAL_NUM_SS CHECK (CAST (SAL_NUM_SS AS BIGINT) BETWEEN 100010100000000 AND 299129999999997)

**escartefigue** · 17/05/2025, 12h17

Encore une fois, si vous avez des individus nés à Monaco, au Maghreb ou dans les TOM, ça ne va pas : certains n° commencent par d'autres chiffres que 1 et 2, il y a des mois lunaire au delà de 12, il y a les numéros provisoires, etc.

À propos de ceci :

Envoyé par Nerva

@escartefigue. Là encore j'avais trouvé un article il y a longtemps sur la nécessité d'éclater le numéro afin de faciliter la conception des requêtes pour des recherches précises d'individus.

S'il s'agit de retrouver les, hommes d'un coté les femmes de l'autre, ou les individus nés tel mois, ça ne fonctionnera pas pour les raisons que j'ai données précédemment.
Il faut stocker, outre le NIR, le code sexe et la date de naissance.

**sevyc64** · 17/05/2025, 14h03

Envoyé par escartefigue

comme le NIR (anciennement NNI) - c'est à dire le n° de sécurité sociale

ATTENTION à ne pas confondre NIR et N° de sécurité sociale. Bien que ce soit très souvent le même n°, ceux sont 2 choses totalement différentes et distinctes

Et ne pas confondre non plus avec les n° temporaires, appelés NTT, qui ne sont en rien des NIR, mais bien des n° de sécurité sociale temporaires, en attendant que la personne en question obtienne un NIR pour que la sécu s'aligne ensuite dessus.

Envoyé par escartefigue

dans certains systèmes d'information, le NIR n'est pas unique.

Le NIR est forcément unique, c'est sa définition même. Et dans le cas de doublons possibles, il existe des mécanismes dans le codage du NIR pour le rendre unique.

Le n° de sécu ne l'est pas forcément. Cas, notamment, entre-autres, des enfants mineurs qui sont couverts au niveau de la sécu par l'un de leur parent. Ils ont alors le n° d'un de leur parent.

Le n° de sécu reprend généralement le NIR de l'assuré.
Le NTT respecte le format du n° de sécu (13 caractères +2 caractères de clé), mais sa codification n'est pas forcément la même, et pas forcément la même d'un n° à l'autre.

Si la structure du NIR habituellement connue est
- 1 caractère pour le sexe (1 ou 2)
- 2 caractères pour l'année de naissance (avec remplissage du zéro à gauche)
- 2 caractères pour le mois de naissance (avec remplissage du zéro à gauche)
- 5 caractères pour le lieu de naissance (habituellement 2 pour le département, 3 pour le n° INSEE de la commune)
- 3 caractères pour un n° d'ordre sans signification

c'est rare, mais le code du sexe n'est pas toujours 1 ou 2, et ça pourrait encore évoluer à l'avenir
Le code du mois de naissance n'est pas forcément de 01 à 12 quand ce mois n'est pas connu
Le lieu de naissance est codifié avec 2 caractères pour le département, 3 pour la commune en France métropolitaine (à noter que pour la corse, ce n'est plus 20, mais 2A 2B qui apparaitra pour les naissances à partir de 1976), mais 3 caractères pour le département, 2 pour la commune pour les DOM et '99' pour le département, code numérique ou alphanumérique du pays pour la "commune" pour les naissances à l'étranger

Comme le NIR est amené à évoluer à l'avenir tout en gardant le format 13 caractères, il est très vivement conseillé pour les diverses administrations de ne pas chercher à le découper, mais à le considérer comme une entité unitaire et entière

**Séb.** · 17/05/2025, 15h58

@seb. Le problème serait le même avec un simple code postal.

Oui, il faut arrêter de comparer des choux (int) et des carottes (text).

Le cas échéant une approche en 2 étapes est possible :
1. La forme ^[0-9]{5}$ ;
2. L'intégrité référentielle selon data.gouv.fr.

**Nerva** · 18/05/2025, 10h39

Envoyé par Séb.

Oui, il faut arrêter de comparer des choux (int) et des carottes (text).

Ça j'en suis conscient mais ignorant comment procéder, j'essaie diverses façons de faire selon les tutoriels que j'ai pu lire.

**escartefigue** · 19/05/2025, 10h00

bonjour,

Envoyé par sevyc64

ATTENTION à ne pas confondre NIR et N° de sécurité sociale. Bien que ce soit très souvent le même n°, ceux sont 2 choses totalement différentes et distinctes

C'est bien la raison pour laquelle j'indiquais ceci :

Envoyé par escartefigue

il existe des NIR particuliers (CAFAT, MONACO, Lunaire...) qui ne répondent pas aux règles de contrôle d'un NIR national. Il ne faut donc pas mettre en place des contrôles de validité tels que vous les avez implémenté si dans vos individus, certains sont nés dans les TOM, à Monaco, au Maghreb...

Envoyé par sevyc64

Le NIR est forcément unique, c'est sa définition même. Et dans le cas de doublons possibles, il existe des mécanismes dans le codage du NIR pour le rendre unique.

S'il est effectivement unique coté sécu, j'indiquais qu'il ne l'est pas toujours coté système d'information local, soit parce qu'on attribue le NIR des parents aux enfants, soit parce qu'on à affaire un numéro non certifié.
Pour avoir longtemps travaillé pour les organismes de retraite complémentaires, ces cas étaient très fréquents (NIR non uniques ou non certifiés dans le SI local)

Sur la conclusion, nous sommes d'accord, il ne faut pas interpréter le contenu de la donnée en la redécoupant, puisqu'il s'agit d'une nomenclature externe, susceptible d'évoluer

Envoyé par escartefigue

comme le NIR (anciennement NNI) - c'est à dire le n° de sécurité sociale - est un code régi par un organisme externe, il devrait être stocké en tant que tel dans une colonne unique et non pas éclaté dans plusieurs colonnes.

Envoyé par sevyc64

Comme le NIR est amené à évoluer à l'avenir tout en gardant le format 13 caractères, il est très vivement conseillé pour les diverses administrations de ne pas chercher à le découper, mais à le considérer comme une entité unitaire et entière

**SQLpro** · 19/05/2025, 18h59

La modélisation de la table n'est pas bonne. En effet la sépartion département commune est une erreur. Un code de commune c'est 5 caractères et inclu le code du département qui peut être de 2 ou 3 caractères (exemple 971...).

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
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)
);

https://esante.gouv.fr/sites/default...l_de_sante.pdf

Enfin la colonne SEXE sert de fourre tout pour certains cas... notamment pour les étrangers

**Nerva** · 23/05/2025, 17h50

Envoyé par SQLpro

La modélisation de la table n'est pas bonne. En effet la sépartion département commune est une erreur.

D'accord pour ça. Mais tel quel on ne peut pas saisir les départements de Corse.

**SQLpro** · 26/05/2025, 14h44

On s'en tire en modifiant comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONSTRAINT CC_PER_SS_CO CHECK (PER_SS_CO IS NULL OR CAST (PER_SS_CO AS INTEGER) BETWEEN '01001' AND '99999' OR PER_SS_CO LIKE '2[A-B][0-9][0-9][0-9]'),

A +

**Michel.Priori** · 26/05/2025, 16h32

Envoyé par SQLpro

On s'en tire en modifiant comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONSTRAINT CC_PER_SS_CO CHECK (PER_SS_CO IS NULL OR CAST (PER_SS_CO AS INTEGER) BETWEEN '01001' AND '99999' OR PER_SS_CO LIKE '2[A-B][0-9][0-9][0-9]'),

Bonjour,
La contrainte CHECK n'est pas là pour vérifier que la réponse est juste, mais pour valider que la valeur saisie est cohérente.
Ainsi un salaire de 5 000 ou 25 000 est cohérent mais -1 ne l'est pas. Et on ne se prend pas le chou à savoir si la saisie est juste.

Dans le cas du N° SS la contrainte de validité est donnée par la clé.
Voir ici : https://xml.insee.fr/schema/nir.html
Tout autre mécanisme de validation sera compliqué à maintenir, dans qq années, par qq d'autre.
Suivre la définition officielle est la meilleure stratégie.

J'en ferais une fonction, histoire qu'elle soit documentée en tant que tel.

Perso, je n'avais retenu que : clé = 97-(Mod(numeross,97)
Mais c'est pas si simple visiblement

**sevyc64** · 27/05/2025, 11h19

Envoyé par Michel.Priori

Perso, je n'avais retenu que : clé = 97-(Mod(numeross,97)
Mais c'est pas si simple visiblement

Si, si, c'est si simple. Il y a juste une petite subtilité dans le calcul de numeross. Pour la Corse, on remplace les n° de départements 2A ou 2B par 20, et ensuite on retire 1 000 000 (pour 2A) ou 2 000 000 (pour 2B) au n° ainsi obtenu, avant de calculer la clé

**Nerva** · 31/05/2025, 15h58

Ma question portait sur le respect du formatage d'un numéro est non sur sa justesse d'un point de vue administratif.

Contraintes de validation d'un numéro de sécurité sociale

Langage SQL

Discussions similaires

Partager

Partager