juillet
2009
Voici un petit exercice récalcitrant : vérifier qu’une date est valide, lorsqu’elle est incomplète…
Soit la table suivante :
CREATE TABLE T_PERSONNE_PRS
(PRS_ID INT NOT NULL PRIMARY KEY,
PRS_NOM CHAR(32) NOT NULL,
PRS_NAISSANCE_AN SMALLINT,
PRS_NAISSANCE_MOIS SMALLINT,
PRS_NAISSANCE_JOUR SMALLINT)
Comment faire en sorte à l’aide de contraintes CHECK (validation) que la date de naissance, même rentrée partiellement, soit valide ?
Voici le jeu de tests pour validation :
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (1, 'Dupont', 1960, 4, 1);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (2, 'Duval', 1960, 2, 29);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (3, 'Martin', 1900, 2, 29);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (4, 'Dufour', 1900, NULL, NULL);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (5, 'Dufaut', 1900, 12, NULL);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (6, 'Dulong', NULL, 12, NULL);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (7, 'Ducong', NULL, 12, 31);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (8, 'Dubourg', NULL, 2, 29);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (9, 'Muller', NULL, 2, 30);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (10, 'Meunier', NULL, 13, NULL);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (11, 'Mallet', NULL, NULL, 32);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (12, 'Meurice', NULL, 13, 32);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (13, 'Mallet', 1975, NULL, 32);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (14, 'Maurier', 1975, 13, NULL)
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (15, 'Dubuis', 1975, NULL, 31);
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_NAISSANCE_AN, PRS_NAISSANCE_MOIS, PRS_NAISSANCE_JOUR)
VALUES (16, 'Dupuis', NULL, NULL, 31);
A la fin de ce lot d’insertion, vous ne devriez avoir dans votre table que les lignes dont les noms des personnes commencent par D, soit 9 lignes :
PRS_ID PRS_NOM PRS_NAISSANCE_AN PRS_NAISSANCE_MOIS PRS_NAISSANCE_JOUR
----------- -------------------------------- ---------------- ------------------ ------------------
1 Dupont 1960 4 1
2 Duval 1960 2 29
4 Dufour 1900 NULL NULL
5 Dufaut 1900 12 NULL
6 Dulong NULL 12 NULL
7 Dulong NULL 12 31
8 Dubourg NULL 2 29
15 Dubuis 1975 NULL 31
16 Dupuis NULL NULL 31
A vous de jouer pour trouver la manière la plus concise d’écrire cette contrainte !
—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *
Salut
Je suis arrivé à ça…
fonction###########
as <br />
begin <br />
declare @rtr smallint; <br />
--convert(datetime,cast(PRS_NAISSANCE_JOUR as varchar(2))+'/'+cast(PRS_NAISSANCE_MOIS as varchar(2))+'/'+cast(PRS_NAISSANCE_AN as varchar(4)),103) as s from T_PERSONNE_PRS <br />
<br />
if(@ms=2) <br />
begin <br />
if ((@an-1980)%4 >0 and @jr>=29) <br />
set @rtr= 0 <br />
goto fin <br />
end <br />
if(@jr=31) <br />
begin <br />
if @ms not in(1,3,5,7,8,10,12) <br />
begin <br />
set @rtr= 0 <br />
goto fin <br />
end <br />
end <br />
else <br />
begin <br />
set @rtr=1 <br />
goto fin <br />
end <br />
fin: <br />
return @rtr <br />
end
#############fonction
J’ai appris lors de cet exercice qu’une fonction n’admet pas de gestion d’erreur!
L’utilisation d’une fonction de test de date est une bonne chose. En dehors des contraintes de domaine applicable dur chacune des colonnes (par exemple vérifier que le mois soit entre 1 et 12 (par un CREATE DOMAIN …), voici une contrainte simple qui résumé les différents cas :
ADD CONSTRAINT CK_PRS_MOIJOU <br />
CHECK (( PPQ_NAISSANCE_JOUR IS NOT NULL <br />
AND PPQ_NAISSANCE_MOIS IS NOT NULL <br />
AND PPQ_NAISSANCE_AN IS NULL <br />
AND PPQ_NAISSANCE_JOUR < <br />
CASE <br />
WHEN PPQ_NAISSANCE_MOIS IN (4, 6, 9, 11) THEN 31 <br />
WHEN PPQ_NAISSANCE_MOIS = 2 THEN 30 <br />
END) <br />
OR PPQ_NAISSANCE_JOUR IS NULL <br />
OR PPQ_NAISSANCE_AN IS NOT NULL <br />
OR PPQ_NAISSANCE_MOIS IS NULL <br />
) <br />
Si l’on écrit une UDF de test de construction de date, par exemple F_DATE_TEST (renvoyant un booléen), alors il faut ajouter la contrainte :
ADD CONSTRAINT CK_PRS_NANMOIJOU <br />
CHECK ( PPQ_NAISSANCE_JOUR IS NULL <br />
OR PPQ_NAISSANCE_MOIS IS NULL <br />
OR PPQ_NAISSANCE_AN IS NULL <br />
OR F_DATE_TEST(PPQ_NAISSANCE_JOUR, PPQ_NAISSANCE_MOIS, PPQ_NAISSANCE_AN)); <br />
Bizare, je n’arrive pas à poster l’ensemble de ma fonction.
Je vous l’envoi par MP.
Bonjour, il y a certainement plus simple, en passant par un trigger par exemple, mais en faisant ceci j’obtient le résultat demandé en PostgreSQL.
Table :
CREATE TABLE T_PERSONNE_PRS <br />
(PRS_ID INT NOT NULL PRIMARY KEY, <br />
PRS_NOM CHAR(32) NOT NULL, <br />
PRS_NAISSANCE_AN SMALLINT, <br />
PRS_NAISSANCE_MOIS SMALLINT, <br />
PRS_NAISSANCE_JOUR SMALLINT, <br />
CONSTRAINT NBR_JOURS CHECK (PRS_NAISSANCE_JOUR BETWEEN 01 AND 31), <br />
CONSTRAINT NBR_MOIS CHECK (PRS_NAISSANCE_MOIS BETWEEN 01 AND 12), <br />
CONSTRAINT CHX_ANNEE CHECK (PRS_NAISSANCE_AN >= 1900), <br />
CONSTRAINT FUNCT_CHECK CHECK (nbr_jour_fevrier(PRS_NAISSANCE_JOUR,PRS_NAISSANCE_MOIS,PRS_NAISSANCE_AN) = 't')) <br />
Fonction :
CREATE FUNCTION nbr_jour_fevrier(smallint, smallint, smallint) RETURNS boolean AS <br />
$BODY$ <br />
DECLARE <br />
jour ALIAS FOR $1; <br />
mois ALIAS FOR $2; <br />
annee ALIAS FOR $3; <br />
<br />
BEGIN <br />
/** <br />
*On vérifie que les 3 champs date ne sont pas vide <br />
*/ <br />
IF jour IS NULL AND mois IS NULL AND annee IS NULL THEN <br />
RETURN 'f'; <br />
<br />
/** <br />
*Si au moins un des champs n'est pas NULL, on vérifie qu'on est en février <br />
*/ <br />
ELSEIF mois = 02 THEN <br />
/** <br />
* Si le champ jour n'est pas NULL, on vérifie que celui est supérieur à 28 (jours maximum en dehors <br />
* de l'année bisextille) <br />
*/ <br />
IF jour IS NOT NULL THEN <br />
<br />
IF jour > 28 THEN <br />
/** <br />
* Si le champ annee n'est pas NULL, on vérifie si il est éventuellemnt une année bisextille <br />
*/ <br />
IF annee IS NOT NULL THEN <br />
IF (annee%4) = 0 AND (annee%100) != 0 AND (annee%400) = 0 THEN <br />
/** <br />
* Si l'année est bisextille, on vérifie qu'on ne dépasse pas 29 jours en février <br />
*/ <br />
IF jour 28 THEN <br />
RETURN 'f'; <br />
END IF; <br />
END IF; <br />
ELSE <br />
RETURN 'f'; <br />
END IF; <br />
ELSE <br />
RETURN 't'; <br />
END IF ; <br />
<br />
ELSE <br />
/** <br />
*Si aucun conditions n'est remplie on retrourne True. <br />
*/ <br />
RETURN 't'; <br />
END IF; <br />
END; <br />
$BODY$ <br />
LANGUAGE plpgsql; <br />