A partir d’une question posée par un internaute sur la façon de procéder pour mettre en place une contrainte de non chevauchement de périodes de temps, j’ai proposé une solution générique et son implémentation dans MS SQL Server et PostGreSQL…

0 – LE MODELE

Voici les tables en question (bien qu’une seule ne nous intéresse, j’ai voulu montré un exemple parfaitement modélisé) :

CREATE TABLE T_PRODUIT_PRD 

(PRD_ID         INT         NOT NULL PRIMARY KEY, 

 PRD_LIBELLE    VARCHAR(64) NOT NULL UNIQUE); 

 

CREATE TABLE T_PRODUIT_DISPO_PDD 

(PRD_ID         INT         NOT NULL,  

 PDD_DEBUT      DATE        NOT NULL,  

 PDD_FIN        DATE,  

 PDD_QUANTITE   FLOAT       NOT NULL, 

 CONSTRAINT FK_PDD_PRD  

    FOREIGN KEY (PRD_ID)  

    REFERENCES T_PRODUIT_PRD (PRD_ID), 

 CONSTRAINT CK_PDD_DEB_FIN  

    CHECK (PDD_DEBUT < PDD_FIN));

La table des produits T_PRODUIT_PRD contient la définition des produits et la table T_PRODUIT_DISPO_PDD les disponibilités du stock pendant une certaine période…. Nous ne nous intéresserons qu’à la table des disponibilités des produits pour laquelle nous voulons éviter toutes périodes recouvrantes pour un même produit…

Voici maintenant quelques données parfaitement qualifiées qui vont nous servir pour nos tests :

INSERT INTO T_PRODUIT_PRD  

VALUES (1, 'Lechitine de soja'), 

       (2, 'Glutamate'); 

 

INSERT INTO T_PRODUIT_DISPO_PDD 

VALUES (1, '2010-01-01', '2010-01-15', 10), 

       (1, '2010-01-20', '2010-01-30', 20), 

       (1, '2010-01-30', '2010-02-10', 30), 

       (2, '2010-01-01', '2010-01-10', 5), 

       (2, '2010-01-11', '2010-01-15', 8);

1 – LA CLEF ET LES CONTRAINTES D’UNICITÉ

Notez que la table T_PRODUIT_DISPO_PDD n’a pas encore de clef…
A ce stade il est important de comprendre qu’un même produit ne peut pas être disponible de manière différente à deux dates identiques, par exemple avoir une disponibilité de 5 entre 1er juillet 2000 et le 10 juillet et une disponibilité de 12 entre le 5 juillet et le 15 juillet de cette même année 2000.
En conclusion :
1) il est impossible d’avoir deux « date de début » identique pour un même produit (unicité)
2) il est impossible d’avoir deux « date de fin » identique pour un même produit (unicité)
Or la date de fin est NULLable, car il se peut que nous ayons l’état du stock avec une date de fin dans le futur, donc, non encore connue, ce que le NULL permet d’indiquer.
Il est alors évident que la clef de cette table est la combinaison PRD_ID + PDD_DEBUT. D’où la contrainte à poser :

ALTER TABLE T_PRODUIT_DISPO_PDD 

   ADD CONSTRAINT PK_PDD 

      PRIMARY KEY (PRD_ID, PDD_DEBUT);

Pour renforcer la sémantique de cette table il nous faut ajouter la contrainte d’unicité sur les colonnes PRD_ID + PDD_FIN :

ALTER TABLE T_PRODUIT_DISPO_PDD 

   ADD CONSTRAINT UK_PDD_PRD_FIN 

      PRIMARY KEY (PRD_ID, PDD_FIN);

Cependant, SQL permet l’insertion de multiples NULL dans une contrainte d’unicité, car seule compte l’unicité des valeurs.
Il faut donc rajouter une contrainte d’unicité absolue. Pour ce faire, nous pourrions utiliser la syntaxe suivante :

ALTER TABLE T_PRODUIT_DISPO_PDD 

   ADD CONSTRAINT CK_PDD_PRD_FIN_UNIQUENULL 

      CHECK (NOT EXISTS(SELECT 0 

                        FROM   T_PRODUIT_DISPO_PDD  

                        WHERE  PDD_FIN IS NULL 

                        GROUP  BY PRD_ID 

                        HAVING COUNT(*) > 1));

Sauf que ni Oracle, ni Microsoft SQL Server, ni PostGreSQL n’acceptent ce genre de contraintes (agrégats relatif à la table ou encore données externes à la table).

• Oracle : ORA-02251 – sous-interrogation non autorisée ici
• SQL Server : Msg 1046, Niveau 15, État 1, Ligne 3 – Les sous-requêtes ne sont pas autorisées dans ce contexte. Seules sont permises les expressions scalaires.
• PostGreSQL : ERREUR: ne peut pas utiliser une sous-requête dans la contrainte de vérification – État SQL :0A000

Pour contourner cela il existe deux solutions : celle du déclencheur ou bien la création d’une contrainte CHECK avec une fonction utilisateur.
Cependant, pour SQL Server, point n’est besoin d’ajouter une telle contrainte, car le défaut de SQL Server sur les contraintes d’unicité s’avère ici un avantage conséquent. En effet, SQL Server n’accepte pas plus d’un NULL dans les colonnes relatives à une contrainte d’unicité, considérant en fait que NULL est une valeur !

Reste donc à pourvoir PostGreSQL d’une telle contrainte…

Voici par exemple comment régler cela pour PostGreSQL avec un déclencheur :

1.1 Рcr̩ation de la fonction de gestion du d̩clencheur pour PostGreSQL :

CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_FIN() RETURNS trigger AS  

$code$ 

DECLARE n_rows integer; 

BEGIN 

SELECT 1 INTO n_rows 

WHERE EXISTS(SELECT 0 

             FROM   T_PRODUIT_DISPO_PDD 

             WHERE  PRD_ID = NEW.PRD_ID 

               AND  PDD_FIN IS NULL 

             GROUP  BY PRD_ID 

             HAVING COUNT(*) > 1); 

IF ( n_rows IS NOT NULL ) 

   THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le couple de colonne PRD_ID + PDD_FIN'; 

   ROLLBACK TRANSACTION; 

END IF;    

RETURN NULL; 

END 

$code$ LANGUAGE 'plpgsql' VOLATILE

1.2 – création du déclencheur associé à la fonction de gestion préalablement définie (PostGreSQL) :

CREATE TRIGGER E_IU_PRD 

   AFTER INSERT OR UPDATE  

   ON T_PRODUIT_DISPO_PDD 

   FOR EACH ROW EXECUTE PROCEDURE F_UNIQUE_NULL_PRD_FIN();

Une autre façon de faire de PostGreSQL, est de réaliser une fonction de vérification et de l’incorporer dans une contrainte CHECK…

Pour supprimer le déclencheur préalablement établis il suffit de lancer l’ordre SQL suivant :
DROP TRIGGER E_IU_PRD ON T_PRODUIT_DISPO_PDD

1.3 Рcr̩ation de la fonction de gestion de la contrainte pour PostGreSQL :

CREATE OR REPLACE FUNCTION F_CHECK_UNIQUE_NULL_PRD_FIN (prd_id INT) 

RETURNS BOOLEAN  

AS  

$code$  

BEGIN 

RETURN CASE 

           WHEN EXISTS(SELECT 0 AS I 

                       FROM   T_PRODUIT_DISPO_PDD 

                       WHERE  PRD_ID = $1 

                       AND    PDD_FIN IS NULL 

                       HAVING COUNT(*) > 1) 

              THEN FALSE 

           ELSE TRUE 

        END;    

END;  

$code$ LANGUAGE 'plpgsql'

1.4 – création de la contrainte associé à la fonction de gestion préalablement définie (PostGreSQL) :

ALTER TABLE T_PRODUIT_DISPO_PDD  

   ADD CONSTRAINT CK_UNIQUE_NULL_PRD_FIN  

      CHECK (F_CHECK_UNIQUE_NULL_PRD_FIN(PRD_ID));

2 – CONTRAINTES POUR LE NON RECOUVREMENT DE PÉRIODES

A nouveau nous devons procéder soit par déclencheurs, soit par des contraintes associées à des fonctions pour interdire le chevauchement des périodes relatives à un même produit.

Solution pour MS SQL Server :

2.1 Рla fonction SQL Server testant le chevauchement des p̩riodes de temps

CREATE FUNCTION F_OVERLAPS (@I1_DEBUT DATE, @I1_FIN   DATE, 

                            @I2_DEBUT DATE, @I2_FIN   DATE) 

RETURNS BIT 

AS 

BEGIN 

   IF @I2_DEBUT >= @I1_FIN RETURN 0; 

   IF @I2_FIN <= @I1_DEBUT RETURN 0; 

   RETURN 1; 

END; 

GO

Attention : cette fonction suppose que les bornes des périodes sont dans le bon sens. D’où l’intérêt de la contrainte de validation CK_PDD_DEB_FIN présente dans la table T_PRODUIT_DISPO_PDD vérifiant que le début est antérieur à la fin.

2.2 РLa fonction SQL Server v̩rifiant le chevauchement de p̩riode de produit :

CREATE FUNCTION F_CHECK_PERIODE_PRODUIT(@PRD_ID INT, @PDD_DEBUT DATE, @PDD_FIN DATE) 

RETURNS BIT 

AS 

BEGIN 

   RETURN CASE WHEN EXISTS(SELECT * 

                           FROM   T_PRODUIT_DISPO_PDD AS T 

                           WHERE  PRD_ID = @PRD_ID 

                             AND  dbo.F_OVERLAPS( PDD_DEBUT, PDD_FIN,  

                                                 @PDD_DEBUT, @PDD_FIN) = 1  

                             AND  (   T.PDD_DEBUT <> @PDD_DEBUT 

                                   OR T.PDD_FIN <>   @PDD_FIN ) )  

                   THEN 1 

               ELSE 0 

          END; 

END;                                                  

GO

2.3 РInt̩gration de la fonction pr̩c̩dente dans une contrainte CHECK de SQL Server :

ALTER TABLE T_PRODUIT_DISPO_PDD 

   ADD CONSTRAINT CK_NON_RECOUVREMENT_PRODUIT 

   CHECK (dbo.F_CHECK_PERIODE_PRODUIT(PRD_ID, PDD_DEBUT, PDD_FIN) = 0); 

GO

Voici maintenant la solution identique pour PostGreSQL :

2.4 РРLa fonction PostGreSQL v̩rifiant le chevauchement de p̩riode de produit :

CREATE OR REPLACE FUNCTION F_CHECK_PERIODE_PRODUIT(prd_id INT, pdd_debut DATE, pdd_fin DATE) 

RETURNS BOOLEAN 

AS 

$code$  

BEGIN 

   RETURN CASE WHEN EXISTS(SELECT * 

                           FROM   T_PRODUIT_DISPO_PDD AS T 

                           WHERE  PRD_ID = $1 

                             AND  overlaps(PDD_DEBUT, PDD_FIN,  

                                           $2,        $3      )  

                             AND  (   T.PDD_DEBUT <> $2 

                                   OR T.PDD_FIN <>   $3 ) )  

                   THEN TRUE 

               ELSE FALSE 

          END; 

END; 

$code$ LANGUAGE 'plpgsql'

NOTA : la fonction OVERLAPS (norme SQL) existe en standard dans PostGreSQL.

2.5 РInt̩gration de la fonction pr̩c̩dente dans une contrainte CHECK de PostGreSQL :

ALTER TABLE T_PRODUIT_DISPO_PDD 

   ADD CONSTRAINT CK_NON_RECOUVREMENT_PRODUIT 

   CHECK (F_CHECK_PERIODE_PRODUIT(PRD_ID, PDD_DEBUT, PDD_FIN)); 

GO

3 – DONNÉES DE TEST

Toutes les insertions suivantes doivent échouer :

Il est amusant de constater que les messages d’erreur de PostGreSQL inversent les paramètres. Sans doute une mauvaise façon d’internationaliser la gestion d’erreur… En effet, le message est :
ERREUR: la nouvelle ligne viole la contrainte de vérification « t_produit_dispo_pdd » de la relation « ck_non_recouvrement_produit »
Il devrait être :
ERREUR: la nouvelle ligne viole la contrainte de vérification « ck_non_recouvrement_produit » de la relation « t_produit_dispo_pdd »

Bien entendu, on pourrait s’intéresser à réaliser cela sous forme de déclencheurs, mais c’est souvent moins performant…

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *