Clé étrangère SQL Server : Comment en créer une avec un exemple

Par : Fiona Brun Fiona Brun
Hours Le kit de préparation mis à jour

⚡ Résumé intelligent

Dans SQL Server, une clé étrangère garantit l'intégrité référentielle en liant une table enfant à une table parente. Chaque valeur de clé étrangère doit déjà exister dans la clé primaire référencée de la table parente.

  • 🔗 Qu'est-ce qu'une clé étrangère ? Une clé étrangère relie une table enfant à une table parent et assure l'intégrité référentielle entre elles.
  • 👪 Parent et enfant : La table référencée est la table parente ; la table contenant la clé étrangère est la table enfant, qui pointe vers la clé primaire de la table parente.
  • 🇧🇷 Deux méthodes de création : Les relations de SQL Server Management Studio et la clause T-SQL CREATE TABLE … FOREIGN KEY … REFERENCES définissent toutes deux une clé étrangère.
  • ➕ Ajouter à un tableau existant : La commande ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY ajoute la relation à une table existante.
  • (I.e. Actions référentielles : Les clauses ON DELETE et ON UPDATE contrôlent les lignes enfants avec NO ACTION, CASCADE, SET NULL ou SET DEFAULT.
  • ✅ Integrity vérifier: L'insertion d'une ligne enfant dont la clé n'a pas de ligne parent correspondante est rejetée.ping Les données sont cohérentes.
En savoir plus

Clé étrangère SQL Server : Comment en créer une dans SQL Server avec un exemple

Qu'est-ce qu'une CLÉ ÉTRANGÈRE ?

Une clé étrangère permet de garantir l'intégrité référentielle au sein d'un système. SQL ServerEn termes simples, une clé étrangère garantit que les valeurs d'une table doivent être présentes dans une autre table.

Règles pour la CLÉ ÉTRANGÈRE

  • La valeur NULL est autorisée dans une clé étrangère SQL.
  • La table à laquelle il est fait référence est appelée la table parente.
  • La table contenant la clé étrangère est appelée table enfant.
  • La clé étrangère dans la table enfant fait référence à clé primaire dans le tableau parent.
  • Cette relation parent-enfant fait respecter la règle connue sous le nom d’« intégrité référentielle ».

Le diagramme ci-dessous récapitule tous les points ci-dessus concernant la clé étrangère.

Diagramme d'une clé étrangère reliant une table enfant à la clé primaire de la table parente

Comment créer une clé étrangère en SQL

Vous pouvez créer une clé étrangère dans SQL Server de deux manières :

SQL Server Management Studio

Table parente : Supposons que nous ayons une table parente existante nommée « Cours ». Course_ID et Course_name sont deux colonnes, Course_ID étant la clé primaire.

Table parente Course avec une clé primaire Course_Id et des colonnes Course_name

Table enfant : Nous devons créer une deuxième table en tant que table enfant. Ses deux colonnes sont « Course_ID » et « Course_Strength ». Cependant, « Course_ID » sera la clé étrangère.

Étape 1) Cliquez avec le bouton droit sur Tables > Nouveau > Table…

Dans SQL Server Management Studio, cliquez avec le bouton droit sur Tables, puis sur Nouveau, puis sur Table.

Étape 2) Saisissez deux noms de colonnes : « Course_ID » et « Course_Strength ». Faites un clic droit sur la colonne « Course_Id », puis cliquez sur Relation.

Nouvelles colonnes de table enfant Course_ID et Course_Strength avec le menu Relations

Étape 3) Dans « Relations de clés étrangères », cliquez sur « Ajouter ».

Boîte de dialogue Relations de clés étrangères avec le bouton Ajouter

Étape 4) Dans « Spécification des tables et des colonnes », cliquez sur l'icône « … ».

Champ de spécification des tableaux et des colonnes avec le bouton des points de suspension

Étape 5) Sélectionnez « COURSE » comme « Table de clé primaire » et la nouvelle table créée comme « Table de clé étrangère » dans la liste déroulante.

Sélectionner COURSE comme table de clé primaire dans la boîte de dialogue des relations

Étape 6) Pour la « Table de clé primaire », sélectionnez la colonne « Course_Id » comme colonne de la table de clé primaire.

Dans le champ « Table de clés étrangères », sélectionnez la colonne « Course_Id » comme colonne de clé étrangère. Cliquez sur OK.

Carteping Course_Id comme colonne de clé primaire et de clé étrangère

Étape 7) Cliquez sur Ajouter.

Cliquer sur Ajouter pour confirmer la relation de clé étrangère

Étape 8) Donnez le nom de la table « Course_Strength » et cliquez sur OK.

Nommez la table enfant Course_Strength et cliquez sur OK.

Résultat : Nous avons établi une relation parent-enfant entre « Course » et « Course_Strength ».

Une relation parent-enfant est établie entre le cours et la force du cours.

ARTICLES LIÉS

T-SQL : Créer une table parent-enfant à l’aide de T-SQL

Table parente : supposons que nous ayons une table parente existante nommée « Cours ». Course_ID et Course_name sont deux colonnes, Course_ID étant la clé primaire.

Table parente existante Course avec Course_Id comme clé primaire

Table enfant : Nous devons créer une deuxième table enfant nommée « Course_Strength_TSQL ». Ses deux colonnes sont « Course_ID » et « Course_Strength ». « Course_ID » sera la clé étrangère.

Voici la syntaxe pour créer un tableau avec une CLÉ ÉTRANGÈRE.

syntaxe:

CREATE TABLE childTable
(
  column_1 datatype [ NULL |NOT NULL ],
  column_2 datatype [ NULL |NOT NULL ],
  ...

  CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n)
    [ ON DELETE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ]
    [ ON UPDATE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ] 
);

Voici une description des paramètres ci-dessus :

  • childTable est le nom de la table à créer.
  • column_1 et column_2 sont les colonnes à ajouter au tableau.
  • fkey_name est le nom de la contrainte de clé étrangère à créer.
  • child_column1, child_column2 … child_column_n sont les colonnes de la table enfant qui font référence à la clé primaire de parentTable.
  • parentTable est le nom de la table parente dont la clé est référencée dans la table enfant.
  • parent_column1, parent_column2 … parent_column_n sont les colonnes qui constituent la clé primaire de la table parente.
  • Le paramètre ON DELETE est facultatif et indique le comportement des données enfants après la suppression des données parentes. Les valeurs possibles sont NO ACTION, SET NULL, CASCADE et SET DEFAULT.
  • Le paramètre ON UPDATE est facultatif et indique le comportement des données enfants après la mise à jour des données parentes. Les valeurs possibles sont NO ACTION, SET NULL, CASCADE et SET DEFAULT.
  • AUCUNE ACTION signifie qu'aucune action n'est entreprise sur les données enfants après la mise à jour ou la suppression des données parents.
  • CASCADE signifie que les données enfants sont supprimées ou mises à jour après que les données parents ont été supprimées ou mises à jour.
  • SET NULL signifie que les données enfants sont définies sur null après la mise à jour ou la suppression des données parentes.
  • L'option « SET DEFAULT » signifie que les données enfants sont réinitialisées à leur valeur par défaut après une mise à jour ou une suppression des données parentes.

Prenons l'exemple d'une clé étrangère qui crée une table avec une colonne servant de clé étrangère, en utilisant une Type de données pour chaque colonne.

Exemple de clé étrangère en SQL

requête:

CREATE TABLE Course_Strength_TSQL
(
Course_ID Int,
Course_Strength Varchar(20) 
CONSTRAINT FK FOREIGN KEY (Course_ID)
REFERENCES COURSE (Course_ID)	
)

Étape 1) Exécutez la requête en cliquant sur Exécuter.

Exécution de la requête CREATE TABLE qui définit la clé étrangère Course_ID

Résultat : Nous avons établi une relation parent-enfant entre 'Course' et 'Course_Strength_TSQL'.

Une relation parent-enfant a été créée entre Course et Course_Strength_TSQL.

Utiliser ALTER TABLE

Nous allons maintenant apprendre à ajouter une clé étrangère à une table existante dans SQL Server à l'aide de l'instruction ALTER TABLE. Nous utiliserons la syntaxe ci-dessous :

ALTER TABLE childTable
ADD CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n);

Voici une description des paramètres utilisés ci-dessus :

  • childTable est le nom de la table à créer.
  • column_1 et column_2 sont les colonnes à ajouter au tableau.
  • fkey_name est le nom de la contrainte de clé étrangère à créer.
  • child_column1, child_column2 … child_column_n sont les colonnes de la table enfant qui font référence à la clé primaire de parentTable.
  • parentTable est le nom de la table parente dont la clé est référencée dans la table enfant.
  • parent_column1, parent_column2 … parent_column_n sont les colonnes qui constituent la clé primaire de la table parente.

Exemple d'ajout d'une clé étrangère avec la commande ALTER TABLE :

ALTER TABLE department
ADD CONSTRAINT fkey_student_admission
    FOREIGN KEY (admission)
    REFERENCES students (admission);

Nous avons créé une clé étrangère nommée fkey_student_admission sur la table département. Cette clé étrangère fait référence à la colonne admission de la table des étudiants.

Exemple de requête CLÉ ÉTRANGÈRE

Commençons par examiner les données de notre table parente, COURSE.

requête:

SELECT * from COURSE;

SELECT résultat affichant les données de la table parente COURSE

Insérons maintenant quelques lignes dans la table enfant 'Course_Strength_TSQL'. Nous allons essayer d'insérer deux types de lignes :

  • Le premier type, pour lequel Course_Id dans la table enfant existe dans Course_Id de la table parent, c'est-à-dire Course_Id = 1 et 2.
  • Le deuxième type, pour lequel Course_Id dans la table enfant n'existe pas dans le Course_Id de la table parent, c'est-à-dire Course_Id = 5.

requête:

Insert into COURSE_STRENGTH values (1,'SQL');
Insert into COURSE_STRENGTH values (2,'Python');
Insert into COURSE_STRENGTH values (5,'PERL');

Insertion des lignes enfants, y compris Course_ID 5 qui n'a pas de parent correspondant

Résultat : Exécutons la requête ensemble pour visualiser nos tables parent et enfant.

Les lignes correspondant aux identifiants de cours 1 et 2 existent dans la table Course_Strength. L'identifiant de cours 5 fait toutefois exception, car il n'a pas de ligne correspondante dans la table parente.

Comparaison des tables parent et enfant ; le cours n° 5 viole l’intégrité référentielle.

FAQ

Une clé primaire identifie de manière unique chaque ligne au sein de sa propre table et ne peut pas être NULL. Une clé étrangère fait référence à cette clé primaire depuis une autre table afin de garantir l'intégrité référentielle. clé primaire versus clé étrangère La comparaison explique toute distinction.

Oui. Une clé étrangère peut faire référence soit à une clé primaire, soit à n'importe quelle colonne comportant une contrainte UNIQUE dans la table parente. La colonne référencée doit contenir des valeurs uniques afin que chaque ligne enfant corresponde exactement à une ligne parente.

L'option ON DELETE CASCADE supprime automatiquement les lignes enfants correspondantes chaque fois que leur ligne parente est supprimée.ping Les tables sont cohérentes. Les alternatives sont SET NULL, qui efface la clé étrangère enfant, et NO ACTION, qui bloque la suppression.

Oui. Une clé étrangère autoréférentielle pointe vers une clé primaire de la même table, modélisant ainsi les hiérarchies, comme par exemple une fiche employé référençant son responsable. Pour les autoréférences, SQL Server recommande l'utilisation de ON DELETE NO ACTION afin d'éviter les cycles de suppression en cascade.

Oui, sauf si la colonne est déclarée NOT NULL. Une clé étrangère NULL signifie que la ligne enfant n'est liée à aucune ligne parente, et SQL Server ignore la vérification référentielle pour cette valeur NULL.

Exécutez la commande ALTER TABLE child_table DROP CONSTRAINT fkey_name. Vous devez indiquer le nom de la contrainte, que vous trouverez dans sys.foreign_keys. Supprimez-la.ping La clé étrangère supprime la relation mais laisse les deux tables et leurs données inchangées.

Oui. Copilote GitHub Il est possible de rédiger des contraintes de clé étrangère dans les instructions CREATE TABLE ou ALTER TABLE à partir d'une invite de commande en langage naturel, qui suggère la table parente et la colonne référencée. Il est impératif de toujours vérifier les clés, les actions référentielles et les types de données avant d'exécuter le script.

Les outils d'IA et d'apprentissage automatique analysent des exemples de données et des modèles de requêtes pour suggérer les colonnes à utiliser comme clés étrangères, détecter les relations manquantes ou orphelines et recommander des actions ON DELETE appropriées. Le développeur examine chaque suggestion avant de l'appliquer.

Résumez cet article avec :