Contraintes uniques et contraintes de validation

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Les contraintes UNIQUE et CHECK sont deux types de contraintes qui peuvent être utilisées pour assurer l’intégrité des données dans les tables SQL Server. Ce sont des objets de base de données importants.

Cet article contient les sections suivantes.

• Contraintes UNIQUE
• Contraintes CHECK
• Tâches associées

Contraintes UNIQUE

Les contraintes sont des règles que le Moteur de base de données SQL Server applique pour vous. Par exemple, vous pouvez utiliser des contraintes UNIQUE pour garantir qu’aucune valeur en double n’est entrée dans des colonnes spécifiques ne faisant pas partie d’une clé primaire. Bien qu'une contrainte UNIQUE et une contrainte PRIMARY KEY assurent l’unicité, il est préférable d’avoir recours à une contrainte UNIQUE au lieu d’une contrainte PRIMARY KEY lorsque vous voulez assurer l’unicité d’une colonne (ou d’une combinaison de colonnes) qui n’est pas la clé primaire.

À la différence des contraintes PRIMARY KEY, les contraintes UNIQUE autorisent la valeur NULL. Cependant, comme pour toute valeur participant à une contrainte UNIQUE, une seule valeur NULL est autorisée par colonne. Une contrainte UNIQUE peut être référencée par une contrainte FOREIGN KEY.

Lorsque vous ajoutez une contrainte UNIQUE à une ou plusieurs colonnes dans la table, par défaut, le Moteur de base de données examine les données existantes dans les colonnes pour s’assurer que toutes les valeurs sont uniques. Si une contrainte UNIQUE est ajoutée à une colonne qui comporte des valeurs dupliquées, le Moteur de base de données renvoie un message d’erreur et n’ajoute pas la contrainte.

Le Moteur de base de données crée automatiquement un index UNIQUE pour appliquer l’impératif d’unicité de la contrainte UNIQUE. Dès lors, en cas de tentative d’insertion d’une ligne dupliquée, le Moteur de base de données renvoie un message d’erreur indiquant que la contrainte UNIQUE a été violée et n’ajoute pas la ligne à la table. Sauf si un index cluster est explicitement spécifié, un index non-cluster unique est créé par défaut pour assurer l'application de la contrainte UNIQUE.

Contraintes CHECK

Les contraintes CHECK assurent l'intégrité de domaine en limitant les valeurs acceptées par une ou plusieurs colonnes. Vous pouvez créer une contrainte CHECK avec n’importe quelle expression logique (booléenne) qui renvoie TRUE ou FALSE sur la base des opérateurs logiques. Par exemple, il est possible de limiter la plage de valeurs d’une colonne salary en créant une contrainte CHECK qui autorise uniquement les données comprises entre 15 000 $ et 100 000 $. De cette façon, il est impossible d'entrer des salaires non compris dans cette fourchette de salaires normaux. L'expression logique est la suivante : salary >= 15000 AND salary <= 100000.

Vous pouvez appliquer plusieurs contraintes CHECK à une seule colonne. Vous pouvez aussi appliquer une seule contrainte CHECK à plusieurs colonnes en la créant au niveau de la table. Ainsi, vous pouvez utiliser une contrainte CHECK sur plusieurs colonnes pour confirmer que toute ligne dont la valeur de la colonne country_region est USA possède également une valeur à deux caractères dans leur colonne state. Cela permet de vérifier plusieurs conditions au même emplacement.

Les contraintes CHECK sont similaires aux contraintes FOREIGN KEY dans la mesure où elles contrôlent les valeurs qui sont placées dans une colonne. Leur différence réside dans la manière dont elles déterminent les valeurs considérées comme valides : les contraintes FOREIGN KEY obtiennent la liste des valeurs valides d’une autre table, tandis que les contraintes CHECK déterminent ces valeurs sur la base d’une expression logique.

Limitations des contraintes CHECK

Les contraintes CHECK rejettent les valeurs qui correspondent à FALSE. Les valeurs nulles étant interprétées comme UNKNOWN, leur présence dans les expressions peut annuler une contrainte. Supposons par exemple que vous placez une contrainte sur une colonne intMyColumn en spécifiant que MyColumn peut contenir uniquement la valeur 10 (MyColumn=10). Si vous insérez la valeur NULL dans MyColumn, le Moteur de base de données insère NULL et ne renvoie aucune erreur.

Une contrainte CHECK renvoie TRUE lorsque la condition qu’elle vérifie n'est pas FALSE pour toute ligne de la table. Une contrainte CHECK s’exécute au niveau de la ligne. Si une table venant d'être créée ne comporte aucune ligne, toute contrainte CHECK sur cette table est considérée comme valide. Cette situation peut produire des résultats inattendus, comme l'illustre l'exemple suivant.

CREATE TABLE CheckTbl (col1 INT, col2 INT);
GO

CREATE FUNCTION CheckFnctn()
RETURNS INT
AS
BEGIN
    DECLARE @retval INT;
    SELECT @retval = COUNT(*)
    FROM CheckTbl;

    RETURN @retval;
END;
GO

ALTER TABLE CheckTbl ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1);
GO

La contrainte CHECK ajoutée spécifie que la table CheckTbldoit contenir au moins une ligne. Toutefois, comme la table ne contient aucune ligne par rapport à laquelle vérifier la condition de cette contrainte, l’instruction ALTER TABLE réussit.

Les contraintes CHECK ne sont pas validées pendant les instructions DELETE. Par conséquent, l'exécution d'instructions DELETE sur des tables avec certains types de contraintes de vérification peuvent produire des résultats inattendus. Imaginons, par exemple, les instructions suivantes exécutées sur la table CheckTbl.

INSERT INTO CheckTbl VALUES (10, 10);
GO
DELETE CheckTbl WHERE col1 = 10;

L'instruction DELETE aboutit, même si la contrainte CHECK spécifie que la table CheckTbl doit comporter au moins 1 ligne.

Tâches associées