Partager via

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

Cette rubrique explique comment créer des relations de clés étrangères dans SQL Server 2014 à 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.

Dans cette rubrique

Avant de commencer

Limitations et restrictions

  • Une contrainte de clé étrangère ne doit pas être liée uniquement à une contrainte de clé primaire dans une autre table ; il peut également être défini 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 vérifier que toutes les valeurs d’une contrainte de clé étrangère composite sont vérifiées, spécifiez NOT NULL sur toutes les colonnes participantes.

  • 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 (Transact-SQL).

  • Les contraintes FOREIGN KEY peuvent faire référence à une autre colonne dans la même table. On appelle habituellement ce mécanisme « auto-référence ».

  • Une contrainte FOREIGN KEY spécifiée au niveau de la colonne ne peut répertorier qu’une seule 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 dans la liste de colonnes de 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.

  • Le moteur de base de données n’a pas de limite prédéfinie sur le nombre de contraintes FOREIGN KEY qu’une table peut contenir qui référencent d’autres tables, ou le nombre de contraintes FOREIGN KEY 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. Nous vous recommandons de ne pas insérer plus de 253 contraintes FOREIGN KEY dans une table et qu'une même table ne soit pas référencée par plus de 253 contraintes FOREIGN KEY.

  • Les contraintes FOREIGN KEY ne sont pas appliquées sur 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) peut participer à une contrainte FOREIGN KEY uniquement si la clé primaire qu’elle référence est également définie comme type varchar(max).

Sécurité

Autorisations

La création d’une table avec une clé étrangère nécessite l’autorisation CREATE TABLE dans la base de données et l’autorisation ALTER sur le schéma dans lequel la table est créée.

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

Utilisation de SQL Server Management Studio

Pour créer une relation de clé étrangère dans l'outil Concepteur de tables

  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 cliquez sur Conception.

    La table s’ouvre dans le Concepteur de tables.

  2. Dans le menu Concepteur de tables , cliquez sur Relations.

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

    La relation s’affiche dans la liste Relation sélectionnée avec un nom fourni par le système au format FK_<tablename_<tablename>, où tablename> est le nom de la table de clé étrangère.

  4. Cliquez sur la relation dans la liste Relations sélectionnées .

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

  6. Dans la boîte de dialogue Tables et colonnes , dans la liste déroulante Clé primaire , choisissez la table qui se trouvera sur le côté clé primaire de la relation.

  7. Dans la grille, choisissez les colonnes qui contribuent à la clé primaire de la table. Dans la cellule de grille adjacente à gauche de chaque colonne, choisissez la colonne de clé étrangère correspondante de la table de clés étrangères.

    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.

Utilisation de Transact-SQL

Pour créer une clé étrangère dans une nouvelle table

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. L’exemple crée une table et définit une contrainte de clé étrangère sur la colonne TempID qui fait référence à la colonne SalesReasonID de la Sales.SalesReason table. Les clauses ON DELETE CASCADE et ON UPDATE CASCADE sont utilisées pour s’assurer que les modifications apportées à la table Sales.SalesReason sont automatiquement propagées à la table Sales.TempSalesReason.

    USE AdventureWorks2012;  
GO  
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  
);GO

Pour créer une clé étrangère dans une table existante

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. L’exemple crée une clé étrangère sur la colonne TempID et fait référence à la colonne SalesReasonID dans la Sales.SalesReason table.

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

    Pour plus d’informations, consultez ALTER TABLE (Transact-SQL), CREATE TABLE (Transact-SQL) et table_constraint (Transact-SQL).

  • Last updated on