Dynamic-Mess.com


"The world is a dynamic mess of jiggling things..."

Les clefs étrangères, principe et exemple avec MySQL

Article posté le 19-01-2015 dans la catégorie SQL

Le but de ce tutoriel est d'expliquer le fonctionnement et l'intérêt de l'utilisation des clefs étrangères dans un SGBD, avec MySQL pour exemple.

1- Principe

Une clef étrangère est une contrainte qui consiste à avoir, dans une table, une colonne dont la valeur est indexée sur le champ d'une autre table.

Exemple imaginons la table suivante

Table Utilisateurs

Id - Int
Nom - Varchar(50)
Prenom - Varchar(50)
Type - Int

On pourrait saisir le type d'utilisateur dans un champ texte ou équivalent. Sauf que le jour où l'on souhaite le modifier sur tous les utilisateurs, cela va devenir compliquer. Alors que si l'on utilisait une clef étrangère, tout se ferait automatiquement puisque la valeur correspondante serait stockée dans une table appropriée. Exemple :

Table Type_Utilisateurs

Id - Int
Nom - Varchar (100)

A présent, dans le champ Type de ma table Utilisateurs, je mettrais l'Id du type d'utilisateur correspondant dans la table Type_Utilisateurs.

Maintenant, faire ceci, c'est déjà pas mal, mais si vous voulez faire cela dans les règles, il faut déclarer ces liens à votre SGBD. En effet, le fait que vous connaissiez les liens ne veut pas dire que

Ainsi en déclarant les liens, une personne qui génèrera le schéma de votre BDD verra de suite les liens représentés, et vous gagnerez du temps lors de la partie codage.

Vocabulaire :

  1. Clef étrangère (FOREIGN KEY) : valeur qui fait référence à une autre table. Dans notre exemple : le champ Type de la table Utilisateurs
  2. Référence (REFERENCES) : valeur de référence dans l'autre table. Dans notre exemple : le champ Id de la table Type_Utilisateurs

2- Pré-requis

Pré-requis propre à tous les SGBD

Pré-requis propre à MySQL

3- Les différents types de règles

Quand vous créez vos clefs étrangères, il existe deux options :

Ces deux options acceptent un paramètre à choisir parmi ces les 4 ci-dessous :

*Cette équivalence entre RESTRICT et NO ACTION est propre à MySQL. Donc attention si vous utilisez un autre SGBD!

4- Exemple de codes avec MySQL

Pour notre exemple, voici le code à utiliser. EDIT : voir la mise à jour récente dans la partie C.

A- Lors de la création de la table

CREATE TABLE Utilisateurs (
Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
Nom VARCHAR(50),
Prenom VARCHAR(50),
Type INT DEFAULT 1,
CONSTRAINT fk_type_utilisateur FOREIGN KEY (Type) REFERENCES Type_Utilisateurs(Id))
ENGINE = InnoDB;

B- Modification d'une table existante

ALTER TABLE Utilisateurs
ADD CONSTRAINT fk_type_utilisateur FOREIGN KEY Type REFERENCES Type_Utilisateurs(Id);

C- Suppression d'une clef étrangère

ALTER TABLE Utilisateurs
DROP FOREIGN KEY fk_type_utilisateur

D- Gestion du comportement du SGBD

Reprenons par exemple notre code de modification, en y ajoutant le fait que nous voulons mettre à NULL si un enregistrement référencé est supprimé, et le mettre à jour s'il est modifié.

ALTER TABLE Utilisateurs
ADD CONSTRAINT fk_type_utilisateur FOREIGN KEY Type REFERENCES Type_Utilisateurs(Id)
ON DELETE SET NULL
ON UPDATE CASCADE;

Note : j'ai rencontré récemment des problèmes avec les dernières versions de SQL. Voici la syntaxe à utiliser :

ALTER TABLE  Utilisateurs ADD FOREIGN KEY (Type) REFERENCES  Type_Utilisateurs (Id) ON DELETE NO ACTION ON UPDATE CASCADE ;

 

Voilà, vous savez l'essentiel! 


Cet article vous a plu? Découvrez d'autres articles


Tweet
comments powered by Disqus