Article mis à jour le : 05-05-2022
Explications sur le fonctionnement et la mise en place des clefs étrangères avec MySQLLe 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.
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 :
Pré-requis propre à tous les SGBD
Pré-requis propre à MySQL
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!
Pour notre exemple, voici le code à utiliser. EDIT : voir la mise à jour récente dans la partie C.
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;
ALTER TABLE Utilisateurs
ADD CONSTRAINT fk_type_utilisateur FOREIGN KEY Type REFERENCES Type_Utilisateurs(Id);
ALTER TABLE Utilisateurs
DROP FOREIGN KEY fk_type_utilisateur
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!