SQL Server n’accepte pas directement les contraintes CHECK complexes. Cet article précise comment faire si l’on désire placer une contrainte CHECK complexe utilisant par exemple un agrégats ou bien faisant référence à une autre table.
Voici un exemple de contrainte CHECK complexe dans une seule et même table faisant appel à un ensemble de lignes (sous requête) et un calcul d’agrégat (max).
CREATE TABLE T_CLIENT
(CLI_ID INTEGER,
CLI_NOM CHAR(32),
CLI_DATE_ENTREE DATE
CLI_REMISE FLOAT,
CONSTRAINT CK_CLI_REMISE <strong>CHECK
( CLI_REMISE <=
(SELECT YEAR(CURRENT_TIMESTAMP)
- YEAR(CLI_DATE_ENTREE)
FROM T_CLIENT
WHERE CLI_DATE_ENTREE = (SELECT MIN(CLI_DATE_ENTREE)
FROM T_CLIEN)))</strong>
Une telle contrainte est parfaitement légitime. Elle signifie qu’aucune remise ne peut excéder le nombre d’année d’ancienneté du plus vieux client…
Cependant SQL Server refuse l’implantation d’une telle contrainte directement. Voici le message d’erreur généré lors de la tentative de création d’une telle contrainte :
Msg 1046, Niveau 15, État 1, Ligne 8
Les sous-requêtes ne sont pas autorisées dans ce contexte. Seules sont permises les expressions scalaires.
Il est cependant possible de passer par une UDF !
1 – Création de l’UDF
CREATE FUNCTION F_REMISE_MAX ()
RETURNS FLOAT
AS
BEGIN
RETURN (SELECT YEAR(CURRENT_TIMESTAMP)
- YEAR(CLI_DATE_ENTREE)
FROM T_CLIENT
WHERE CLI_DATE_ENTREE = (SELECT MIN(CLI_DATE_ENTREE)
FROM T_CLIEN))
END
2 – implémentation de la contrainte CHECK utilisant l’UDF
CREATE TABLE T_CLIENT2
(CLI_ID INTEGER,
CLI_NOM CHAR(32),
CLI_DATE_ENTREE DATE,
CLI_REMISE FLOAT,
CONSTRAINT CK_CLI_REMISE CHECK (CLI_REMISE <= dbo.F_REMISE_MAX())
)
CQFD !
--------
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 * * * * *
En principe c’est plus performant… si c’est bien écrit ! Car la contrainte est préventive tandis que le trigger est postérieur au niveau transactionnel !
J’y avais même pas pensé !!! J’aurai eu tendance à écrire un trigger pour ce genre de contrainte…
Est-ce plus performant que d’utiliser un trigger ?
Merci en tout cas pour l’astuce !