Partager via

  • Article

Contraintes FOREIGN KEY

On appelle « clé étrangère » (FK, foreign key) une colonne ou une combinaison de colonnes utilisée pour établir et conserver une liaison entre les données de deux tables. Vous pouvez créer une clé étrangère en définissant une contrainte FOREIGN KEY lors de la création ou de la modification d'une table.

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 de la base de données AdventureWorks possède une liaison avec 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 de clé primaire de la table SalesPerson. La colonne SalesPersonID de la table SalesOrderHeader est donc la clé étrangère de la table SalesPerson.

SalesOrderHeader.SalesPersonID est une clé étrangère.

Une contrainte FOREIGN KEY ne doit pas forcément être liée seulement à une contrainte PRIMARY KEY d'une autre table. Elle peut également être définie pour référencer les colonnes d'une contrainte UNIQUE d'une autre table. Une contrainte FOREIGN KEY peut contenir des valeurs NULL. Toutefois, si l'une des colonnes d'une contrainte FOREIGN KEY composite contient des valeurs NULL, la vérification de l'ensemble des valeurs composant cette contrainte FOREIGN KEY sera omise. Pour vous assurer que toutes les valeurs d'une contrainte FOREIGN KEY composite sont vérifiées, spécifiez NOT NULL pour toutes les colonnes participant à la contrainte.

[!REMARQUE]

Une contrainte FOREIGN KEY peut référencer les colonnes des tables dans une même base de données ou dans une même table. C'est ce que l'on appelle les tables auto-référencées. Prenons par exemple une table employés qui contient les trois colonnes suivantes : employee_number, employee_name et manager_employee_number. Étant donné que le responsable est également un employé, il existe une relation de clé étrangère entre la colonne manager_employee_number et la colonne employee_number.

Intégrité référentielle

Bien que la principale utilité d'une contrainte FOREIGN KEY 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 de ce vendeur est utilisé pour les commandes de la table Sales.SalesOrderHeader, l'intégrité relationnelle entre les deux tables est rompue. Les commandes du vendeur supprimé deviennent des enregistrements orphelins dans la table SalesOrderHeader dépourvus de tout lien vers des données de la table SalesPerson.

Une contrainte FOREIGN KEY empêche une telle situation. La contrainte conserve l'intégrité référentielle en garantissant qu'aucune modification ne peut être apportée aux données de la table de clés primaires si ces modifications rendent non valide la liaison avec les données de la table de clés étrangères. En cas de tentative de suppression de la ligne d'une table de clés primaires ou de modification d'une valeur de clé primaire, l'action échoue si la valeur de clé primaire supprimée ou modifiée correspond à une valeur de la contrainte FOREIGN KEY d'une autre table. Pour parvenir à modifier ou à supprimer une ligne d'une contrainte FOREIGN KEY, 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.

Indexation des contraintes FOREIGN KEY

La création d'un index pour une clé étrangère s'avère parfois utile pour les raisons suivantes :

  • Les modifications apportées aux contraintes PRIMARY KEY sont vérifiées parallèlement aux contraintes FOREIGN KEY des tables associées.

  • Des 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 FOREIGN KEY d'une table avec la ou les colonnes de clé unique ou primaire de l'autre table. L'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. La création de cet index n'est toutefois pas obligatoire. Les données des deux tables associées peuvent être combinées même si aucune contrainte PRIMARY KEY ou FOREIGN KEY 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. Pour plus d'informations sur l'utilisation des contraintes FOREIGN KEY avec des jointures, consultez Principes de base des jointures et Types de requêtes et index.

Nombre de contraintes FOREIGN KEY dans une table

Dans SQL Server, il n'existe aucune limite prédéfinie en ce qui concerne le nombre de contraintes FOREIGN KEY pouvant être contenues dans une table (qui font référence à d'autres tables) et le nombre de contraintes FOREIGN KEY détenues par d'autres tables référençant une table spécifique. Le nombre réel de contraintes FOREIGN KEY est toutefois limité en fonction de la configuration de votre matériel, ainsi que des paramètres de votre base de données et de vos applications. Nous vous recommandons de limiter les tables à un maximum de 253 contraintes FOREIGN KEY et leur référencement par un maximum de 253 contraintes FOREIGN KEY. Prenez toujours en compte les coûts d'application des contraintes FOREIGN KEY lors de la configuration de votre base de données et de vos applications.