Microsoft SQL Server
Clés étrangères

Relation / contrainte de clé étrangère

Les clés étrangères vous permettent de définir la relation entre deux tables. Une table (parent) doit avoir une clé primaire qui identifie de manière unique les lignes de la table. Une autre table (enfant) peut avoir la valeur de la clé primaire du parent dans l'une des colonnes. La contrainte FOREIGN KEY REFERENCES garantit que les valeurs de la table enfant doivent exister en tant que valeur de clé primaire dans la table parent.

Dans cet exemple, nous avons la table de la société mère avec la clé primaire CompanyId et la table de l'employé enfant avec l'ID de la société où cet employé travaille.

create table Company (
   CompanyId int primary key,
   Name nvarchar(200)
)
create table Employee (
    EmployeeId int,
    Name nvarchar(200),
    CompanyId int
        foreign key references Company(companyId)
)

Les références de clé étrangère garantissent que les valeurs insérées dans la colonne Employee.CompanyId doivent également exister dans la colonne Company.CompanyId. En outre, personne ne peut supprimer la société dans la table de la société s’il ya au moins un employé avec un ID de société correspondant dans la table enfant.

La relation FOREIGN KEY garantit que les lignes de deux tables ne peuvent pas être "dissociées".

Maintien de la relation entre les lignes parent / enfant

Supposons que nous ayons une ligne dans la table Company avec companyId 1. Nous pouvons insérer une ligne dans la table employee avec companyId 1:

insert into Employee values (17, 'John', 1)

Cependant, nous ne pouvons pas insérer d'employé dont CompanyId n'existe pas:

insert into Employee values (17, 'John', 111111)

Msg 547, niveau 16, état 0, ligne 12 L'instruction INSERT était en conflit avec la contrainte FOREIGN KEY "FK__Employee__Compan__1EE485AA". Le conflit s'est produit dans la base de données "MyDb", table "dbo.Company", colonne "CompanyId". La déclaration a été terminée.

En outre, nous ne pouvons pas supprimer la ligne parente dans la table de l'entreprise tant qu'il y a au moins une ligne enfant dans la table des employés qui la référence.

delete from company where CompanyId = 1

Msg 547, Niveau 16, État 0, Ligne 14 L'instruction DELETE était en conflit avec la contrainte REFERENCE "FK__Employee__Compan__1EE485AA". Le conflit s'est produit dans la base de données "MyDb", table "dbo.Employee", colonne "CompanyId". La déclaration a été terminée.

La relation de clé étrangère garantit que les lignes de la société et des employés ne seront pas "dissociées".

Ajout d'une relation de clé étrangère sur une table existante

La contrainte FOREIGN KEY peut être ajoutée aux tables existantes qui ne sont toujours pas en relation. Imaginez que nous ayons des tables Société et Employé où la colonne Employé de la table CompanyId n'a pas de relation de clé étrangère. L'instruction ALTER TABLE vous permet d'ajouter une contrainte de clé étrangère sur une colonne existante qui référence une autre table et une clé primaire dans cette table:

alter table Employee
    add  foreign key (CompanyId) references Company(CompanyId)

Ajouter une clé étrangère sur la table existante

Les colonnes FOREIGN KEY avec contrainte peuvent être ajoutées aux tables existantes qui ne sont toujours pas en relation. Imaginez que nous ayons des tables Company et Employee où la table Employee n'a pas de colonne CompanyId. L'instruction ALTER TABLE vous permet d'ajouter une nouvelle colonne avec une contrainte de clé étrangère qui référence une autre table et une clé primaire dans cette table:

alter table Employee
    add CompanyId int foreign key references Company(CompanyId)

Obtenir des informations sur les contraintes de clés étrangères

La vue système sys.foreignkeys renvoie des informations sur toutes les relations de clés étrangères dans la base de données:

select name,
 OBJECT_NAME(referenced_object_id) as [parent table],
 OBJECT_NAME(parent_object_id) as [child table],
 delete_referential_action_desc,
 update_referential_action_desc
from sys.foreign_keys