Contraintes de clé primaire et étrangère

Les clés primaires et les clés étrangères sont deux types de contraintes qui peuvent être utilisées pour appliquer l'intégrité des données dans des tables SQL Server. Ce sont des objets de base de données importants.

Cette rubrique contient les sections suivantes.

Contraintes de clé primaire

Contraintes de clé étrangère

Tâches associées

Contraintes de clé primaire

Une table contient généralement une colonne ou une combinaison de colonnes dont les valeurs identifient de façon unique chaque ligne dans la table. Cette colonne (ou ces colonnes), appelée clé primaire (PK, Primary Key), assure l'intégrité de l'entité de la table. Étant donné que les contraintes de clé primaire garantissent des données uniques, elles sont fréquemment définies sur une colonne d’identité.

Lorsque vous spécifiez une contrainte de clé primaire pour une table, le moteur de base de données garantit l'unicité des données en créant automatiquement un index unique pour les colonnes de clé primaire. Cet index permet également l'accès rapide aux données lorsque la clé primaire est utilisée dans des requêtes. Si une contrainte de clé primaire est définie sur plusieurs colonnes, les valeurs peuvent être dupliquées dans une colonne, mais chaque combinaison de valeurs de toutes les colonnes de la définition de contrainte de clé primaire doit être unique.

Comme illustré dans l’illustration suivante, les colonnes ProductID et VendorID de la table Purchasing.ProductVendor forment une contrainte de clé primaire composite pour cette table. Cela permet de s’assurer que chaque ligne de la table ProductVendor a une combinaison unique de ProductID et vendorID. Cela empêche l'insertion de lignes en double.

• Une table ne peut contenir qu'une seule contrainte de clé primaire.

• Une clé primaire ne peut pas dépasser 16 colonnes et une longueur totale de clé de 900 octets.

• L’index généré par une contrainte de clé primaire ne peut pas entraîner le nombre d’index sur la table à dépasser 999 index non cluster et 1 index cluster.

• Si « clusterisé » ou « non-clusterisé » n’est pas spécifié pour une contrainte de clé primaire, « clusterisé » est utilisé s’il n’y a pas d’indice clusterisé sur la table.

• Toutes les colonnes définies dans une contrainte de clé primaire doivent avoir une valeur autre que Null. Si la valeur Nullability n’est pas spécifiée, toutes les colonnes participant à une contrainte de clé primaire ont leur valeur nullabilité définie sur non null.

• Si une clé primaire est définie sur une colonne avec le type de données CLR défini par l'utilisateur, l'implémentation du type doit prendre en charge le tri binaire.

Contraintes de clé étrangère

On appelle « clé étrangère » une colonne ou une combinaison de colonnes utilisée pour établir et conserver une liaison entre les données de deux tables pour contrôler les données qui peuvent être stockées dans la table de clés étrangères. Dans une référence de clé étrangère, la création d'une liaison entre deux tables s'effectue lors du référencement de la ou des colonnes contenant les valeurs de clé primaire d'une table dans la ou les colonnes de l'autre table. Cette colonne devient alors une clé étrangère dans la seconde table.

Par exemple, la table Sales.SalesOrderHeader a un lien de clé étrangère vers la table Sales.SalesPerson , car il existe une relation logique entre les commandes et les vendeurs. La colonne SalesPersonID de la table SalesOrderHeader correspond à la colonne clé primaire de la table SalesPerson . La colonne SalesPersonID de la table SalesOrderHeader est la clé étrangère de la table SalesPerson . En créant cette relation de clé étrangère, une valeur pour SalesPersonID ne peut pas être insérée dans la table SalesOrderHeader si elle n’existe pas déjà dans la table SalesPerson .

Index sur les contraintes de clé étrangère

Contrairement aux contraintes de clé primaire, la création d’une contrainte de clé étrangère ne crée pas automatiquement un index correspondant. Toutefois, la création manuelle d'un index pour une clé étrangère s'avère souvent utile pour les raisons suivantes :

• Les colonnes de clé étrangère sont souvent employées dans les critères de jointure lorsque les données de tables associées sont combinées dans des requêtes grâce à la correspondance de la ou des colonnes de la contrainte de clé étrangère d'une table avec la ou les colonnes de clé primaire ou unique de l'autre table. Un index permet au moteur de base de données de rechercher rapidement des données associées dans la table de clés étrangères. Toutefois, la création de cet index n’est pas nécessaire. Les données des deux tables associées peuvent être combinées même si aucune contrainte de clé primaire ou étrangère n'est définie entre les tables. La présence d'une relation de clé étrangère entre deux tables indique que toutes deux ont été optimisées en vue de leur combinaison dans une requête utilisant les clés comme critères.

• Les modifications apportées aux contraintes de clé primaire sont vérifiées parallèlement aux contraintes de clé étrangère des tables associées.

Intégrité référentielle

Bien que la principale utilité d'une contrainte de clé étrangère consiste à contrôler les données qui peuvent être stockées dans la table de clés étrangères, elle contrôle également les modifications apportées aux données de la table de clés primaires. Par exemple, si la ligne d’un vendeur est supprimée de la table Sales.SalesPerson et que l’ID du vendeur est utilisé pour les commandes dans la table Sales.SalesOrderHeader , l’intégrité relationnelle entre les deux tables est rompue ; les commandes des vendeurs supprimés sont orphelines dans la table SalesOrderHeader sans lien vers les données de la table SalesPerson .

Une contrainte de clé étrangère empêche une telle situation. La contrainte applique l’intégrité référentielle en garantissant que les modifications ne peuvent pas être apportées aux données dans la table de clés primaires si ces modifications invalident le lien vers les données de la table de clés étrangères. Si une tentative est effectuée pour supprimer la ligne d’une table de clés primaires ou pour modifier une valeur de clé primaire, l’action échoue lorsque la valeur de clé primaire supprimée ou modifiée correspond à une valeur dans la contrainte de clé étrangère d’une autre table. Pour parvenir à modifier ou à supprimer une ligne d'une contrainte de clé étrangère, vous devez d'abord supprimer ou modifier les données de clé étrangère de la table de clés étrangères, ce qui crée une liaison entre la clé étrangère et d'autres données de clé primaire.

Intégrité référentielle en cascade

Les contraintes d'intégrité référentielle en cascade définissent les actions exécutées par le moteur de base de données lorsqu'un utilisateur tente de supprimer ou de mettre à jour une clé vers laquelle pointent des clés étrangères existantes. Les actions en cascade suivantes peuvent être définies.

AUCUNE ACTION Le moteur de base de données génère une erreur et l’action de suppression ou de mise à jour sur la ligne de la table parente est annulée.

Les lignes correspondantes CASCADE sont mises à jour ou supprimées dans la table de référence lorsque cette ligne est mise à jour ou supprimée dans la table parente. CASCADE ne peut pas être spécifiée si une timestamp colonne fait partie de la clé étrangère ou de la clé référencée. ON DELETE CASCADE ne peut pas être spécifié pour une table qui a un déclencheur INSTEAD OF DELETE. ON UPDATE CASCADE ne peut pas être spécifié pour les tables qui ont des déclencheurs INSTEAD OF UPDATE.

SET NULL Toutes les valeurs qui composent la clé étrangère sont définies sur NULL lorsque la ligne correspondante de la table parente est mise à jour ou supprimée. Pour que cette contrainte s'applique, les colonnes clés étrangères doivent pouvoir cependant être définies sur NULL. Impossible de spécifier ceci pour les tables qui ont des déclencheurs INSTEAD OF UPDATE.

SET DEFAULT Toutes les valeurs qui composent la clé étrangère sont définies sur leurs valeurs par défaut si la ligne correspondante de la table parente est mise à jour ou supprimée. Pour que cette contrainte s'applique, les colonnes clés étrangères doivent disposer cependant de valeur par défaut. Si une colonne peut être affectée de la valeur NULL et qu'aucune valeur par défaut n'est définie, NULL constitue alors la valeur par défaut de la colonne de façon implicite. Ne peut pas être spécifié pour les tables qui ont des déclencheurs INSTEAD OF UPDATE.

Il est possible de combiner CASCADE, SET NULL, SET DEFAULT et NO ACTION pour des tables liées par des relations référentielles. Si le moteur Moteur de base de données rencontre NO ACTION, il s’interrompt et restaure les actions CASCADE, SET NULL et SET DEFAULT. Lorsqu'une instruction DELETE génère une combinaison d'actions CASCADE, SET NULL, SET DEFAULT et NO ACTION, les actions CASCADE, SET NULL et SET DEFAULT sont appliquées par le Moteur de base de données avant toute recherche de NO ACTION.

Déclencheurs et actions référentielles en cascade

Les actions référentielles en cascade déclenchent les déclencheurs AFTER UPDATE ou AFTER DELETE de la manière suivante :

• Toutes les actions référentielles en cascade directement provoquées par la méthode DELETE ou UPDATE d’origine sont effectuées en premier.

• S’il existe des déclencheurs AFTER définis sur les tables affectées, ces déclencheurs se déclenchent une fois que toutes les actions en cascade sont effectuées. Ces déclencheurs sont activés dans l'ordre inverse de l'action en cascade. S’il existe plusieurs déclencheurs sur une seule table, ils se déclenchent dans un ordre aléatoire, sauf s’il existe un premier ou un dernier déclencheur dédié pour la table. Cet ordre est spécifié à l’aide de sp_settriggerorder.

• Si plusieurs chaînes en cascade proviennent de la table qui était la cible directe d’une action UPDATE ou DELETE, l’ordre dans lequel ces chaînes déclenchent leurs déclencheurs respectifs n’est pas spécifié. Toutefois, une chaîne active toujours tous ses déclencheurs avant qu'une autre chaîne ne soit activée.

• Un déclencheur de type AFTER sur la table qui est la cible directe d'une action UPDATE ou DELETE se déclenche, que des lignes soient affectées ou non. Dans ce cas, aucune autre table n'est affectée par les actions en cascade.

• Si l’un des déclencheurs précédents effectue des opérations UPDATE ou DELETE sur d’autres tables, ces actions peuvent démarrer des chaînes en cascade secondaires. Ces chaînes secondaires sont traitées individuellement pour chaque opération UPDATE ou DELETE, après le déclenchement de tous les déclencheurs sur toutes les chaînes primaires. Ce processus peut être répété de manière récursive pour les opérations UPDATE ou DELETE suivantes.

• L’exécution d’opérations CREATE, ALTER, DELETE ou d’autres opérations de langage de définition de données (DDL) à l’intérieur des déclencheurs peut entraîner le déclenchement des déclencheurs DDL. Cela peut ensuite effectuer des opérations DELETE ou UPDATE qui démarrent des chaînes et des déclencheurs en cascade supplémentaires.

• Si une erreur est générée à l’intérieur d’une chaîne d’action référentielle en cascade particulière, une erreur est générée, aucun déclencheur AFTER n’est déclenché dans cette chaîne, et l’opération DELETE ou UPDATE qui a créé la chaîne est restaurée.

• Une table qui a un déclencheur INSTEAD OF ne peut pas également avoir une clause REFERENCES qui spécifie une action en cascade. Toutefois, un déclencheur AFTER sur une table ciblée par une action en cascade peut exécuter une instruction INSERT, UPDATE ou DELETE sur une autre table ou vue qui déclenche un déclencheur INSTEAD OF défini sur cet objet.

Tâches associées

Le tableau suivant répertorie les tâches courantes associées aux contraintes de clé primaire et de clé étrangère.