Contraintes de clé primaire et étrangère

• 07/25/2017
• 9 minutes de lecture
• • s
  • M
  • M
  • c
  • J
  • +1

Applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance

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

Cette rubrique contient les sections suivantes.

Contraintes de clé primaire

Contraintes de clé étrangère

Tâches connexes

Contraintes de clé primaire

Une table possède généralement une colonne ou une combinaison de colonnes contenant des valeurs qui identifient de manière unique chaque ligne de la table. Cette colonne, ou ces colonnes, est appelée la clé primaire (PK) de la table et renforce l’intégrité de l’entité de la table. Comme 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 applique l’unicité des données en créant automatiquement un index unique pour les colonnes de clé primaire. Cet index permet également un 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 plus d’une colonne, les valeurs peuvent être dupliquées dans une colonne, mais chaque combinaison de valeurs de toutes les colonnes dans la définition de la contrainte de clé primaire doit être unique.

Comme le montre 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 possède 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 de clé totale de 900 octets.

• L’index généré par une contrainte de clé primaire ne peut pas faire en sorte que le nombre d’index sur la table dépasse 999 index non groupés et 1 index groupé.

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

• Toutes les colonnes définies dans une contrainte de clé primaire doivent être définies comme non nulles. Si la nullité n’est pas spécifiée, toutes les colonnes participant à une contrainte de clé primaire ont leur nullité définie sur not null.

• Si une clé primaire est définie sur une colonne de type défini par l’utilisateur du CLR, l’implémentation du type doit supporter l’ordre binaire.

Contraintes de clé étrangère

Une clé étrangère (FK) est une colonne ou une combinaison de colonnes qui est utilisée pour établir et appliquer un lien entre les données de deux tables pour contrôler les données qui peuvent être stockées dans la table de clé étrangère. Dans une référence de clé étrangère, un lien est créé entre deux tables lorsque la ou les colonnes qui détiennent la valeur de la clé primaire d’une table sont référencées par la ou les colonnes d’une autre table. Cette colonne devient une clé étrangère dans la deuxième table.

Par exemple, la table Sales.SalesOrderHeader a un lien de clé étrangère avec la table Sales.SalesPerson car il existe une relation logique entre les commandes de vente et les vendeurs. La colonne SalesPersonID de la table SalesOrderHeader correspond à la colonne de 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.

Une table peut référencer un maximum de 253 autres tables et colonnes en tant que clés étrangères (références sortantes). SQL Server 2016 (13.x) augmente la limite du nombre d’autres tables et colonnes qui peuvent référencer les colonnes d’une seule table (références entrantes), de 253 à 10 000. (Nécessite au moins le niveau de compatibilité 130.) L’augmentation comporte les restrictions suivantes :

• Les références de clés étrangères supérieures à 253 sont uniquement prises en charge pour les opérations DML DELETE. Les opérations UPDATE et MERGE ne sont pas prises en charge.

• Une table avec une référence de clé étrangère à elle-même est toujours limitée à 253 références de clé étrangère.

• Plus de 253 références de clé étrangère ne sont pas actuellement disponibles pour les index columnstore, les tables optimisées pour la mémoire, Stretch Database ou les tables à clé étrangère partitionnées.

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. Cependant, la création manuelle d’un index sur une clé étrangère est souvent utile pour les raisons suivantes :

• Les colonnes de clé étrangère sont fréquemment utilisées dans les critères de jointure lorsque les données de tables liées sont combinées dans des requêtes en faisant correspondre la ou les 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 trouver rapidement les données liées dans la table de clé étrangère. Toutefois, la création de cet index n’est pas obligatoire. Les données de deux tables liées peuvent être combinées même si aucune contrainte de clé primaire ou de clé étrangère n’est définie entre les tables, mais une relation de clé étrangère entre deux tables indique que les deux tables ont été optimisées pour être combinées dans une requête qui utilise les clés comme critères.

• Les modifications des contraintes de clé primaire sont vérifiées avec les contraintes de clé étrangère dans les tables liées.

Intégrité référentielle

Bien que l’objectif principal d’une contrainte de clé étrangère soit de contrôler les données qui peuvent être stockées dans la table de clé étrangère, elle contrôle également les modifications des données dans la table de clé primaire. 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 de vente dans la table Sales.SalesOrderHeader, l’intégrité relationnelle entre les deux tables est rompue ; les commandes de vente du vendeur supprimé sont orphelines dans la table SalesOrderHeader sans lien avec les données de la table SalesPerson.

Une contrainte de clé étrangère empêche cette situation. La contrainte applique l’intégrité référentielle en garantissant que des modifications ne peuvent pas être apportées aux données de la table à clé primaire si ces modifications invalident le lien avec les données de la table à clé étrangère. Si l’on tente de supprimer une ligne dans une table à clé primaire ou de modifier une valeur de clé primaire, l’action échouera si 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 réussir à modifier ou à supprimer une ligne dans une contrainte de clé étrangère, vous devez d’abord soit supprimer les données de clé étrangère dans la table de clé étrangère, soit modifier les données de clé étrangère dans la table de clé étrangère, ce qui lie la clé étrangère à différentes données de clé primaire.

Intégrité référentielle en cascade

En utilisant des contraintes d’intégrité référentielle en cascade, vous pouvez définir les actions que le moteur de base de données prend 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 soulève une erreur et l’action de suppression ou de mise à jour de la ligne dans la table parente est annulée.

CASCADE
Les lignes correspondantes 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é si une colonne d’horodatage fait partie soit de la clé étrangère, soit 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 mises à NULL lorsque la ligne correspondante dans la table parente est mise à jour ou supprimée. Pour que cette contrainte s’exécute, les colonnes de la clé étrangère doivent être annulables. Ne peut pas être spécifié 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 dans la table parente est mise à jour ou supprimée. Pour que cette contrainte s’exécute, toutes les colonnes de clé étrangère doivent avoir des définitions par défaut. Si une colonne est annulable et qu’aucune valeur par défaut explicite n’est définie, NULL devient la valeur par défaut implicite de la colonne. Ne peut pas être spécifié pour les tables qui ont des déclencheurs INSTEAD OF UPDATE.

CASCADE, SET NULL, SET DEFAULT et NO ACTION peuvent être combinés sur des tables qui ont des relations référentielles entre elles. Si le moteur de base de données rencontre NO ACTION, il arrête et annule les actions CASCADE, SET NULL et SET DEFAULT associées. Lorsqu’une instruction DELETE provoque une combinaison d’actions CASCADE, SET NULL, SET DEFAULT et NO ACTION, toutes les actions CASCADE, SET NULL et SET DEFAULT sont appliquées avant que le moteur de base de données ne vérifie la présence d’un 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 le DELETE ou UPDATE d’origine sont exécutées en premier.

• S’il existe des déclencheurs AFTER définis sur les tables concernées, ces déclencheurs se déclenchent après l’exécution de toutes les actions en cascade. Ces déclencheurs se déclenchent dans l’ordre inverse de l’action en cascade. S’il y a plusieurs déclencheurs sur une seule table, ils se déclenchent dans un ordre aléatoire, sauf s’il y a un premier ou un dernier déclencheur dédié pour la table. Cet ordre est celui spécifié par l’utilisation 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é. Cependant, une chaîne déclenche toujours tous ses déclencheurs avant qu’une autre chaîne ne commence à tirer.

• Un déclencheur AFTER sur la table qui est la cible directe d’une action UPDATE ou DELETE se déclenche indépendamment du fait que des lignes soient affectées. Il n’y a pas d’autres tables affectées par la mise en cascade dans ce cas.

• 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 de mise en cascade secondaires. Ces chaînes secondaires sont traitées pour chaque opération UPDATE ou DELETE à la fois après que tous les déclencheurs de toutes les chaînes primaires se déclenchent. 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 provoquer le déclenchement de déclencheurs DDL. Ceux-ci peuvent ensuite effectuer des opérations DELETE ou UPDATE qui lancent 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’actions référentielles en cascade particulière, une erreur est levé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 annulée.

• Une table qui possède 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 connexes

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

Tâche	Sujet
Décrit comment créer une clé primaire.	Créer des clés primaires
Décrit comment supprimer une clé primaire.	Supprimer des clés primaires
Décrit comment modifier une clé primaire.	Modifier des clés primaires
Décrit comment créer des relations de clé étrangère	Créer des relations de clé étrangère
Décrit comment modifier des relations de clé étrangère.	Modifier des relations de clé étrangère
Décrit comment supprimer des relations de clé étrangère.	Supprimer des relations de clé étrangère
Décrit comment visualiser les propriétés de clé étrangère.	Voir les propriétés des clés étrangères
Décrit comment désactiver les contraintes de clé étrangère pour la réplication.	Désactiver les contraintes de clé étrangère pour la réplication
Décrit comment désactiver les contraintes de clé étrangère pendant une instruction INSERT ou UPDATE.	Désactiver les contraintes de clé étrangère avec les déclarations INSERT et UPDATE

.