Clé étrangère en SQL : définition et guide pratique

La clé étrangère est un outil essentiel dans une base de données (BDD) relationnelle.

Elle permet de mettre en relation les différentes tables de la BDD.

C’est aussi une contrainte qui assure l’intégrité référentielle de celle-ci. Il est donc important de bien comprendre le fonctionnement de la clé étrangère pour exploiter correctement des données en SQL.

Pour aborder correctement  la clé étrangère en SQL, il est important de bien comprendre les bases de données qui mettent en relation plusieurs tables.

Une table est composée de lignes et de colonnes comme sur un tableau Excel :

• Chaque ligne correspond à un enregistrement. Un enregistrement est composé de plusieurs données, réparties dans plusieurs colonnes. 
• Chaque donnée correspond à un champ. Un enregistrement est donc composé de plusieurs champs.
• Chaque colonne correspond à un attribut qui permet de classifier un champ.
  

Par exemple, considérons une base de données d’une boutique en ligne. Celle-ci peut être composée de deux types de données :

• Les données sur les différentes commandes du magasin : Nom du produit et prix
• Les données sur les clients du magasin : Prénom, nom, adresse.

Si on utilise un tableau Excel pour gérer cette base de données, on est obligé de répéter inutilement des informations concernant le client.

‍

‍

Pour éviter cette répétition on va diviser cette base de données en deux tables :

• « Commande » : Contient la liste des différentes commandes du magasin.
• « Client » : Contient la liste des différents clients du magasin.

‍

‍

‍

On allège ainsi la base de données en retirant toutes les répétitions. 

Cependant, il faut maintenant trouver un moyen d’associer chaque commande à un client. 

Il faut mettre en relation les deux tables pour associer les clients Jean Bonneau et Ambre Cerna aux différentes commandes de la boutique.

C’est ici que la clé étrangère entre en jeu ! 

‍

{{cours-gratuit-sql="/brouillon"}}

‍

Pourquoi utiliser une clé étrangère?

La clé étrangère  permet : 

• De mettre en relation deux tables au sein d’une BDD relationnelle.
• D’assurer l’intégrité référentielle des données. Autrement dit, seules les valeurs devant apparaître dans la base de données sont permises.

‍

La clé étrangère met en relation deux tables

La clé étrangère fait référence à la clé primaire d’une autre table.

Pour expliquer le rôle de la clé étrangère dans la mise en relation de deux tables, reprenons l’exemple de la boutique en ligne.

Voici comment mettre en relation la table “Commande” avec la table “Client” :

‍

Nous avons ajouté les clés primaires de chaque table et une clé étrangère dans la table « Commande » 

‍

Dans la table « Commande », la clé étrangère est l’attribut « Id Client ». En effet, celle-ci référence la clé primaire « Id Client » de la table « Client ».

‍

Maintenant que la colonne «Id Client» se trouve dans les deux tables, il est facile de retrouver les commandes effectuées par Ambre Cerna. Il nous suffit de ne garder que les commandes dont l’identifiant est 2. Idem pour Jean Bonneau, son identifiant est 1.

La clé étrangère « Id client » met donc en relation la table « Commande » et la table « Client ».

‍

 {{banniere-article}}

‍

Clé étrangère et intégrité référentielle de la base de données

La clé étrangère est une contrainte qui s’assure du respect de l’intégrité référentielle de la base de données.

Concrètement, une donnée qui compose la clé étrangère d’une table A doit faire référence à une donnée existante dans la clé primaire d’une table B.

Dans la boutique en ligne, la contrainte est qu’une commande doit nécessairement être associée à un « Id Client» qui est déjà référencé dans la table  « Client ». Sinon, la clé étrangère pointe vers du vide et la mise en relation est impossible !

Pour faire cela, nous avons définit une clé étrangère dans la table « Commande » : « Id Client». Ainsi, la table « Commande » n’accepte que des « Id Client» qui existe dans la table « Client ».

De cette manière, on s’assure que la table « Commande » contient uniquement des informations sur des clients existants dans la table « Client ».

En définissant une clé étrangère, nous avons donc respecté l’intégrité référentielle dans la base de données de la boutique en ligne !

‍

Comment définir une clé étrangère?

La manière de définir une clé étrangère dépend de l’outil que tu utilises. Ici, nous te montrons comment procéder avec deux outils différents : SQL Server et MySQL.

Définir une clé étrangère avec une requête SQL Server

Définir la clé étrangère à la création de la table

Le langage SQL permet de définir, de manipuler ou de contrôler les données d’une BDD relationnelle.

Il est donc possible de créer une nouvelle table avec la requête CREATE TABLE.

Ici, nous allons voir comment définir une clé étrangère lors de la création d’une table.

Comme exemple, nous allons créer une table « Commande » pour notre boutique en ligne. Cette table est composée de 5 attributs dont la clé étrangère « Id client » :

‍

CREATE TABLE Commande (
    Id_commande INT PRIMARY KEY,
    Nom_du_produit VARCHAR(15),
    Prix INT,
    Date_de_la_commande DATE,
    Id_client INT REFERENCES Client(Id_client)
);

‍

Dans cette requête SQL, la première ligne consiste à créer et à définir le nom de la table. Les lignes 2,3,4, 5 et 6 définissent les noms des différentes colonnes ainsi que la nature des données qu’on trouve à l’intérieur : INT, VARCHAR ou DATE.

En effet, chaque attribut est associé à un nom et à un type de donnée :

• INT signifie “Integer” : il s’agit d’un chiffre entier
• VARCHAR signifie “Variable character” : il s’agit d’une chaîne de caractères
• DATE fait référence à une date

Remarque : les chiffres entre parenthèses définissent le nombre de caractères maximum qu’on peut trouver au sein d’un champ. Dans l’exemple ci-dessus, les champs de la colonne “Nom du produit” font 15 caractères maximum.

Pour définir la clé étrangère de la table, nous avons ajouté « REFERENCES client (Id client) » à la ligne 6, après avoir précisé le type de données.

Ainsi, « Id client » dans la table « Commande » fait référence à la clé primaire « Id client » dans la table « Client ».

« Id client » est la clé étrangère de notre table « Commande » !

‍
Cette requête nous donne une table « Commande » qui est vide pour l’instant.

Définir la clé étrangère sur une table déjà existante

Dans la vie de la base de données, il peut arriver que l’on doive ajouter une nouvelle table avec une clé primaire. Cependant, il faut que cette table puisse entrer en relation avec les autres tables qui composent la base de données. Pour cela, on doit ajouter une clé étrangère dans une table déjà existante (et elle doit pointer vers la clé primaire de la nouvelle table).

Imaginons que notre boutique en ligne atteigne un stade de développement qui l’oblige à faire appel à plusieurs entreprises de livraison.

Pour suivre ses colis expédiés, il devient judicieux de créer une table “Livraison” qui contient les informations sur les colis et sur l’entreprise chargée de l'expédition.

Une fois la table « Livraison » créée, nous devons ajouter une clé étrangère dans la table « Commande » afin de mettre en relation les deux tables :

‍

ALTER TABLE Commande
ADD FOREIGN KEY (Livraison_ID)
REFERENCES Livraison(Livraison_ID);

‍

Dans cette requête :

• la première ligne permet de d’ajouter une colonne dans la table « Commande »,
• la seconde ligne définit la clé étrangère « Livraison ID »,
• la troisième ligne indique que « Livraison ID » fait référence à la table « Livraison ».

‍

{{formation-sql="/brouillon"}}

‍

Définir une clé étrangère avec une requête MySQL

MySQL est un système de gestion de bases de données relationnelles SQL open source.

Dans cet outil, la définition de la table ressemble à celle de SQL à quelques différences près.

Prenons à nouveau la création de la table « Commande » avec pour clé étrangère « Id client » :

‍

CREATE TABLE Commande (
    Id_commande INT,
    Nom_du_produit VARCHAR(15),
    Prix INT,
    Date_de_la_commande DATE,
    Id_client INT,
    PRIMARY KEY (Id_commande),
    FOREIGN KEY (Id_client) REFERENCES Client(Id_client)
);

‍

La ligne 8 correspond à la définition de la clé étrangère :

• FOREIGN KEY permet de définir l’attribut qui est la clé étrangère.
• REFERENCES permet d’indiquer que l’attribut « Id Client » fait référence à la clé primaire de la table de «Client».
  

Limitation et contraintes de la clé étrangère

La clé étrangère doit suivre une série de contraintes : 

• La clé étrangère ne peut faire référence qu’à une colonne (ou des colonnes) au sein de la même base de données, sur le même serveur.
• La colonne de la clé étrangère et celle qu’elle référence doivent être de même type (INT, VARCHAR etc…).
• Une clé étrangère ne peut pas être appliquée dans des tables temporaires.
  

Fais donc bien attention à respecter ces contraintes quand tu manipules une clé étrangère !

Le monde de la Data t’intéresse ? Viens jeter un coup d’œil à nos formations en Data Analyse et en SQL, et deviens-toi aussi un Databirdie !

Exploitation de la clé étrangère avec une jointure

Maintenant que tu connais l’intérêt d’une clé étrangère dans une base de donnée relationnelle, voyons comment exploiter cet outil grâce à une technique très connue des Data analystes : les jointures.

Concrètement, une jointure SQL permet de fusionner tout ou partie de plusieurs tables afin d’extraire les informations que tu souhaites analyser. Pour y arriver, il faut donc utiliser la ou les clés étrangères à ta disposition.

‍
Prenons la base de données de notre boutique en ligne :

Sur cette base de données on peut effectuer des analyses ciblées pour mieux connaître notre clientèle.

Par exemple, on veut connaître la liste de toutes les commandes effectuées par Jean Bonneau avec ses informations de livraison.

Pour cela on va réaliser une jointure sur les trois tables. Cette jointure va utiliser les clésétrangères « Livraison ID » et « Id Client » qui mettent en relation les tables

‍

SELECT IdCommande, produit, prix, livreur, numero_de_colis
FROM Commande
INNER JOIN Client ON Commande.IdClient = Client.IdClient
INNER JOIN Livraison ON Commande.LivraisonId = Livraison.LivraisonId
WHERE Commande.IdClient = 1;

‍

On obtient le résultat suivant : 

‍

Grâce à la clé étrangère, nous avons réussi à associer plusieurs tables et à filtrer les données pour n’avoir que les commandes associées à un client !

‍

{{formation-sql="/brouillon"}}