SQL FOREIGN KEY : Comment créer dans SQL Server avec un exemple

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

A Clé étrangère fournit un moyen de renforcer l'intégrité référentielle au sein de SQL Server. En termes simples, la 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

  • NULL est autorisé dans la clé étrangère SQL.
  • La table référencée est appelée Table Parent.
  • La table avec la clé étrangère en SQL est appelée Child Table.
  • La clé étrangère SQL dans la table enfant fait référence à la clé primaire dans la table parent.
  • Cette relation parent-enfant applique la règle connue sous le nom d’« intégrité référentielle ».

L'exemple de clé étrangère ci-dessous dans SQL avec diagramme résume tous les points ci-dessus pour FOREIGN KEY

Règles pour FOREIGN KEY en SQL
Comment fonctionne la clé étrangère

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

Nous pouvons créer un Clé étrangère dans le serveur SQL dans Façons 2:

  1. SQL Server Management Studio
  2. T-SQL

SQL Server Management Studio

Tableau parent : Supposons que nous ayons une table parent existante sous le nom de « Cours ». Course_ID et Course_name sont deux colonnes avec Course_Id comme clé primaire.

Créer une CLÉ ÉTRANGÈRE en SQL

Tableau enfant : Nous devons créer la deuxième table en tant que table enfant. « Course_ID » et « Course_Strength » sur deux colonnes. Cependant, « Course_ID » doit être une clé étrangère.

Étape 1) Faites un clic droit sur Tables>Nouveau> Table…

Créer une CLÉ ÉTRANGÈRE en SQL

Étape 2) Entrez le nom de deux colonnes comme « Course_ID » et « Course_Strength ». Faites un clic droit sur la colonne « Course_Id ». Cliquez maintenant sur Relation.

Créer une CLÉ ÉTRANGÈRE en SQL

Étape 3) In 'Relation de clé étrangère,' Cliquez sur 'Ajouter'

Créer une CLÉ ÉTRANGÈRE en SQL

Étape 4) Dans « Spécifications des tables et des colonnes », cliquez sur Icône '…'

Créer une CLÉ ÉTRANGÈRE en SQL

Étape 5) Sélectionnez « Table de clés primaires » comme « COURS » et la nouvelle table en cours de création comme « Table de clés étrangères » dans la liste déroulante.

Créer une CLÉ ÉTRANGÈRE en SQL

Étape 6) « Table de clés primaires » – Sélectionnez la colonne « Course_Id » comme colonne « Table de clés primaires ».

« Tableau des clés étrangères » - Sélectionnez la colonne « Course_Id » comme colonne « Tableau des clés étrangères ». Cliquez sur OK.

Créer une CLÉ ÉTRANGÈRE en SQL

Étape 7) Cliquez sur Ajouter.

Créer une CLÉ ÉTRANGÈRE en SQL

Étape 8) Donnez le nom de la table comme « Course_Strength » et cliquez sur D'ACCORD.

Créer une CLÉ ÉTRANGÈRE en SQL

Résultat: Nous avons défini la relation parent-enfant entre 'Cours' et 'Cours_force.'

Créer une CLÉ ÉTRANGÈRE en SQL

T-SQL : créer une table parent-enfant à l'aide de T-SQL

Tableau parent : Reconsidérez-le, nous avons une table parent existante avec le nom de table « Cours ».

Course_ID et Course_name sont deux colonnes avec Course_Id comme clé primaire.

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

Tableau enfant : Nous devons créer la deuxième table en tant que table enfant avec le nom « Course_Strength_TSQL ».

'Course_ID' et 'Course_Strength' comme deux colonnes pour la table enfant Course_Strength_TSQL.' Cependant, « Course_ID » doit être une clé étrangère.

Ci-dessous la syntaxe pour créer une table avec FOREIGN KEY

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.
  • colonne_1, colonne_2- les colonnes à ajouter au tableau.
  • fkey_name- le nom de la contrainte de clé étrangère à créer.
  • child_column1, child_column2…child_column_n- le nom des colonnes chidTable pour référencer la clé primaire dans parentTable.
  • parentTable- le nom de la table parent dont la clé doit être référencée dans la table enfant.
  • parent_column1, parent_column2, … parent_column3- les colonnes constituant la clé primaire de la table parent.
  • SUR SUPPRIMER. Un paramètre facultatif. Il spécifie ce qui arrive aux données enfants après la suppression des données parent. Certaines des valeurs de ce paramètre incluent NO ACTION, SET NULL, CASCADE ou SET DEFAULT.
  • SUR MISE À JOUR - Un paramètre facultatif. Il spécifie ce qui arrive aux données enfants après la mise à jour des données parent. Certaines des valeurs de ce paramètre incluent NO ACTION, SET NULL, CASCADE ou SET DEFAULT.
  • AUCUNE ACTION - utilisé avec ON DELETE et ON UPDATE. Cela signifie que rien n'arrivera aux données enfants après la mise à jour ou la suppression des données parent.
  • CASCADE- utilisé avec ON DELETE et ON UPDATE. Les données enfants seront supprimées ou mises à jour après la suppression ou la mise à jour des données parents.
  • SET NULL- utilisé avec ON DELETE et ON UPDATE. L'enfant sera défini sur null une fois les données parent mises à jour ou supprimées.
  • SET DEFAULT - utilisé avec ON DELETE et ON UPDATE. Les données enfants seront définies sur les valeurs par défaut après une mise à jour ou une suppression des données parents.

Voyons un exemple de clé étrangère en SQL pour créer une table avec une colonne comme clé étrangère :

Exemple de clé étrangère dans 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.

Clé étrangère en SQL

Résultat: Nous avons défini la relation parent-enfant entre 'Cours' et 'Cours_strength_TSQL.'

Clé étrangère en SQL

Utiliser ALTER TABLE

Nous allons maintenant apprendre à utiliser la clé étrangère dans SQL et à ajouter une clé étrangère dans le serveur SQL à l'aide de l'instruction ALTER TABLE, nous utiliserons la syntaxe donnée 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.
  • colonne_1, colonne_2- les colonnes à ajouter au tableau.
  • fkey_name- le nom de la contrainte de clé étrangère à créer.
  • child_column1, child_column2…child_column_n- le nom des colonnes chidTable pour référencer la clé primaire dans parentTable.
  • parentTable- le nom de la table parent dont la clé doit être référencée dans la table enfant.
  • parent_column1, parent_column2, … parent_column3- les colonnes constituant la clé primaire de la table parent.

Exemple de modification de table et d'ajout de clé étrangère :

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

Tout d'abord, voyons nos données de table parent, COURSE.

requête:

SELECT * from COURSE;

Exemple de requête CLÉ ÉTRANGÈRE

Insérons maintenant une ligne dans la table Child : 'Cours_strength_TSQL.'

Nous allons essayer d'insérer deux types de lignes

  1. Le premier type, pour lequel Course_Id dans la table enfant existera dans Course_Id de la table Parent. c'est-à-dire Course_Id = 1 et 2
  2. Le deuxième type, pour lequel Course_Id dans la table enfant n'existe pas dans la table 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');

Exemple de requête CLÉ ÉTRANGÈRE

Résultat: Exécutons la requête ensemble pour voir notre table Parent et Enfant

La ligne avec Course_ID 1 et 2 existe dans la table Course_strength. Alors que Course_ID 5 est une exception.

Exemple de requête CLÉ ÉTRANGÈRE

Résumé

  • Chaque valeur de la clé étrangère doit faire partie de Clé primaire d'autres tableaux.
  • La clé étrangère MySQL peut faire référence à une autre colonne de la même table. Cette référence est appelée auto-référence.
  • Contrainte de clé étrangère SQL : permet de sécuriser les liens entre les tables et les données invalides à insérer dans la colonne Clé étrangère.
  • Vous pouvez créer une clé étrangère en utilisant Create Table, Alter Table ou SQL Server Management Studio.
  • Voici la différence entre la clé primaire et la clé étrangère : Cliquez ici