Créer des relations de clé étrangère

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed Instance

Cet article explique comment créer des relations de clé étrangère dans SQL Server à l’aide de SQL Server Management Studio ou de Transact-SQL. Vous créez une relation entre deux tables lorsque vous voulez associer des lignes d'une table à des lignes appartenant à une autre table.

Autorisations

La création d'une nouvelle table avec une clé étrangère nécessite une autorisation CREATE TABLE dans la base de données et une autorisation ALTER pour le schéma dans lequel la table a été créée.

La création d'une clé étrangère dans une table existante nécessite l'autorisation ALTER sur la table.

Limites et restrictions

  • Une contrainte de clé étrangère ne doit pas nécessairement être liée uniquement à une contrainte de clé primaire dans une autre table. Les clés étrangères peuvent également être définies pour référencer les colonnes d’une contrainte UNIQUE dans une autre table.

  • Lorsqu'une valeur différente de NULL est entrée dans la colonne d'une contrainte FOREIGN KEY, la valeur doit exister dans la colonne référencée. Dans le cas contraire, le système retourne un message d'erreur signalant une violation de clé étrangère. Pour vous assurer que toutes les valeurs d'une contrainte de clé étrangère composite sont vérifiées, spécifiez NOT NULL pour toutes les colonnes participant à la contrainte.

  • Les contraintes FOREIGN KEY ne peuvent faire référence qu'à des tables au sein de la même base de données sur le même serveur. L'intégrité référentielle inter-base de données doit être implémentée via les déclencheurs. Pour plus d’informations, consultez CREATE TRIGGER.

  • Les contraintes FOREIGN KEY peuvent faire référence à une autre colonne dans la même table, ce qui est appelé une auto-référence.

  • Une contrainte FOREIGN KEY spécifiée au niveau de la colonne ne peut lister qu'une colonne de référence. Cette colonne doit avoir le même type de données que la colonne pour laquelle la contrainte est définie.

  • Une contrainte FOREIGN KEY spécifiée au niveau de la table doit avoir le même nombre de colonnes de référence que le nombre de colonnes de la liste des colonnes de la contrainte. Le type de données de chaque colonne de référence doit également être identique à la colonne de référence correspondante dans la liste des colonnes.

  • LeMoteur de base de données n’a pas de limite prédéfinie quant au nombre de contraintes de clé étrangère qu’une table peut contenir et qui référencent d’autres tables. Le Moteur de base de données ne limite pas non plus le nombre de contraintes de clé étrangère détenues par d’autres tables qui font référence à une table spécifique. Cependant, le nombre réel de contraintes FOREIGN KEY qui peuvent être utilisées est limité par la configuration matérielle et par la conception de la base de données et de l'application. Une table peut référencer au maximum 253 autres tables et colonnes en tant que clés étrangères (références sortantes). SQL Server 2016 (13.x) et versions ultérieures font passer de 253 à 10 000 le nombre limite des autres tables et colonnes pouvant référencer des colonnes dans une table unique (références entrantes). (Cela nécessite au minimum le niveau de compatibilité 130). Cette augmentation est soumise aux restrictions suivantes :

    • Les références de clés étrangères supérieures à 253 sont prises en charge pour les opérations DELETE et UPDATE DML. Les opérations MERGE ne sont pas prises en charge.
    • Une table comportant une clé étrangère référencée vers elle-même est toujours limitée à 253 références de clés étrangères.
    • Les références de clés étrangères supérieures à 253 ne sont actuellement disponibles ni pour les index columnstore, ni pour les tables à mémoire optimisée, ni pour Stretch Database.

    Important

    Stretch Database est déprécié dans SQL Server 2022 (16.x). Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

  • Les contraintes FOREIGN KEY ne sont pas appliquées dans les tables temporaires.

  • Si une clé étrangère 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. Pour plus d’informations, consultez Types CLR définis par l’utilisateur.

  • Une colonne de type varchar(max) ne peut participer à une contrainte FOREIGN KEY que si la clé primaire qu’elle référence est également définie comme étant de type varchar(max) .

Créer une relation de clé étrangère dans le Concepteur de tables

Utiliser SQL Server Management Studio

  1. Dans l’Explorateur d’objets, cliquez avec le bouton droit sur la table qui se trouve du côté clé étrangère de la relation, puis sélectionnez Conception.

    La table s'ouvre dans le Concepteur de tables.

  2. Dans le menu Concepteur de tables , sélectionnez Relations. (Consultez le menu Concepteur de tables dans l’en-tête ou cliquez avec le bouton de droite dans l’espace vide de la définition de table, puis sélectionnez Relations... .)

  3. Dans la boîte de dialogue Relations de clé étrangère , sélectionnez Ajouter.

    La relation s’affiche dans la liste Relation sélectionnée avec un nom fourni par le système au format FK_<nom de table>_<nom de table>, où le premier nom de table est le nom de la table de clés étrangères et le deuxième nom de table est le nom de la table de clés primaires. Il s’agit simplement d’une convention d’affectation de noms par défaut et commune pour le champ (Nom) de l’objet clé étrangère.

  4. Sélectionnez la relation dans la liste Relation sélectionnée.

  5. Sélectionnez Spécification de tables et colonnes dans la grille sur la droite, puis sélectionnez les points de suspension ( ... ), à droite de la propriété.

  6. Dans la liste déroulante Clé primaire de la boîte de dialogue Tables et colonnes , choisissez la table qui sera du côté clé primaire de la relation.

  7. Dans la grille située au-dessous, choisissez les colonnes qui participent à la clé primaire de la table. Dans la cellule de la grille située à droite de chaque colonne, choisissez la colonne clé étrangère correspondante dans la table de clé étrangère.

    LeConcepteur de tables propose un nom pour la relation. Pour changer ce nom, modifiez le contenu de la zone de texte Nom de la relation .

  8. Choisissez OK pour créer la relation.

  9. Fermez la fenêtre du concepteur de tables et enregistrez vos modifications pour que la modification de la relation de clé étrangère prennent effet.

Créer une clé étrangère dans une nouvelle table

Utiliser Transact-SQL

L’exemple suivant crée une table et définit une contrainte de clé étrangère sur la colonne TempID qui référence la colonne SalesReasonID dans la table Sales.SalesReason de la base de données AdventureWorks. Les clauses ON DELETE CASCADE et ON UPDATE CASCADE sont utilisées pour garantir que les modifications apportées à la table Sales.SalesReason sont automatiquement propagées dans la table Sales.TempSalesReason .

CREATE TABLE Sales.TempSalesReason 
   (
      TempID int NOT NULL, Name nvarchar(50)
      , CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID)
      , CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
        REFERENCES Sales.SalesReason (SalesReasonID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
   )
;

Créer une clé étrangère dans une table existante

Utiliser Transact-SQL

L’exemple suivant crée une clé étrangère sur la colonne TempID et référence à la colonne SalesReasonID dans la table Sales.SalesReason de la base de données AdventureWorks.

ALTER TABLE Sales.TempSalesReason
   ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
      REFERENCES Sales.SalesReason (SalesReasonID)
      ON DELETE CASCADE
      ON UPDATE CASCADE
;

Étapes suivantes