Contraintes complexes : gestion du non recouvrement de périodes

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 :

INSERT INTO T_PRODUIT_DISPO_PDD VALUES (1, '2010-01-01', '2001-01-15', 99); --> même période : viol de PK
INSERT INTO T_PRODUIT_DISPO_PDD VALUES (1, '2010-01-01', '2010-01-18', 99); --> même début : viol de PK  
INSERT INTO T_PRODUIT_DISPO_PDD VALUES (1, '2010-01-02', '2010-01-15', 99); --> même fin : viol unicité PRD/FIN  
INSERT INTO T_PRODUIT_DISPO_PDD VALUES (1, '2010-01-02', '2003-05-14', 99); --> incluse : viol CHECK recouvrement
INSERT INTO T_PRODUIT_DISPO_PDD VALUES (1, '2009-12-31', '2010-01-16', 99); --> recouvrante : viol CHECK recouvrement
INSERT INTO T_PRODUIT_DISPO_PDD VALUES (1, '2009-12-15', '2010-01-05', 99); --> superposiation antérieure : viol CHECK recouvrement
INSERT INTO T_PRODUIT_DISPO_PDD VALUES (1, '2010-01-10', '2010-01-18', 10); --> superposiation postérieure : viol CHECK recouvrement

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  * * * * *

MVP Microsoft SQL Server

Laisser un commentaire