Suite à un post sur la soit-disante impossibilité de créer, dans PostGreSQL, une contraintes CHECK vérifiant des données externes à la table dans laquelle elle était placée, je me suis fait un malin plaisir de démontrer le contraire…

Voici un exemple de contrainte CHECK sous PostGreSQL vérifiant depuis une tables des conditions avec des données d’une autre table… Il s’agit d’empêcher par une contrainte que la remise sur la facture ne dépasse pas la remise maximale prévue pour le client… Bien entendue la remise maximale possible figure dans la table des clients, tandis que celle accordée à chaque facture se trouve… dans la table des factures !

Tables pour l’exemple : clients et factures :

CREATE TABLE T_CLIENT_CLI 

(CLI_ID          INT NOT NULL PRIMARY KEY, 

 CLI_NOM         VARCHAR(32) NOT NULL, 

 CLI_REMISE_MAX  FLOAT NOT NULL DEFAULT 0 CHECK (CLI_REMISE_MAX BETWEEN 0.00 AND 100.00)); 

 

CREATE TABLE T_FACTURE_FCT 

(FCT_ID          INT NOT NULL PRIMARY KEY, 

 CLI_ID          INT NOT NULL, 

 FCT_DATE        DATE NOT NULL DEFAULT CURRENT_DATE, 

 FCT_REMISE      FLOAT NOT NULL DEFAULT 0 CHECK (FCT_REMISE BETWEEN 0.00 AND 100.00), 

 CONSTRAINT FK FOREIGN KEY (CLI_ID) REFERENCES T_CLIENT_CLI (CLI_ID));

Création d’une fonction de vérification (UDF) :

CREATE OR REPLACE FUNCTION F_CHECK_MAX_REMISE (client_id int, remise float) 

RETURNS BOOLEAN AS 

$$ 

SELECT CASE  

          WHEN CLI_REMISE_MAX < $2 THEN false 

          ELSE true 

        END  

FROM T_CLIENT_CLI WHERE CLI_ID = $1; 

$$ LANGUAGE 'sql';

Ajout de la contrainte dans la table des factures, à l’aide de la fonction de vérification :

ALTER TABLE T_FACTURE_FCT 

   ADD CONSTRAINT K CHECK (F_CHECK_MAX_REMISE (CLI_ID, FCT_REMISE));

insertion d’une client test :
INSERT INTO T_CLIENT_CLI VALUES (1, 'DUPONT', 15);

Insertion d’une « bonne » facture :
INSERT INTO T_FACTURE_FCT VALUES (101, 1, '2011-04-15', 12.0)

Insertion d’une « mauvaise » facture :
INSERT INTO T_FACTURE_FCT VALUES (102, 1, '2011-04-15', 35.0)

Et réponse du serveur…
Message d’erreur :
Citation:
ERREUR: la nouvelle ligne viole la contrainte de vérification « t_facture_fct » de la relation « k »
********** Erreur **********
ERREUR: la nouvelle ligne viole la contrainte de vérification « t_facture_fct » de la relation « k »
État SQL :23514

CQFD !

Pour information….

Ceci figure dans mes bouquins sur SQL depuis la deuxième édition (2008)…

-------- 

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