Options des clés étrangères
Lorsque je vous ai parlé des clés étrangères, et que je vous ai donné la syntaxe pour les créer, j'ai omis de vous parler des deux
options fort utiles :
ON DELETE, qui permet de déterminer le comportement de MySQL en cas de suppression d'une référence ;
ON UPDATE, qui permet de déterminer le comportement de MySQL en cas de modification d'une référence.
Nous allons maintenant examiner ces options.
Option sur suppression des clés étrangères
Petits rappels
La syntaxe
Voici comment on ajoute une clé étrangère à une table déjà existante :
Code : SQL
ALTER TABLE nom_table
ADD [CONSTRAINT fk_col_ref]
-- On donne un nom à la clé
(facultatif)
FOREIGN KEY colonne
-- La colonne sur laquelle on
ajoute la clé
REFERENCES table_ref(col_ref); -- La table et la colonne de
référence
Le principe expliqué ici est exactement le même si l'on crée la clé en même temps que la table. La commande ALTER
TABLE est simplement plus courte, c'est la raison pour laquelle je l'utilise dans mes exemples plutôt que CREATE
TABLE.
Le principe
Dans notre table Animal, nous avons par exemple mis une clé étrangère sur la colonne race_id , référençant la colonne id de la
table Race. Cela implique que chaque fois qu'une valeur est insérée dans cette colonne (soit en ajoutant une ligne, soit en
modifiant une ligne existante), MySQL va vérifier que cette valeur existe bien dans la colonne id de la table Race.
Aucun animal ne pourra donc avoir un race_id qui ne correspond à rien dans notre base.
Suppression d'une référence
Que se passe-t-il si l'on supprime la race des Boxers ? Certains animaux référencent cette espèce dans leur colonne race_id . On
risque donc d'avoir des données incohérentes. Or, éviter cela est précisément la raison d'être de notre clé étrangère.
Essayons :
Code : SQL
DELETE FROM Race WHERE nom = 'Boxer';
Code : Console
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Ouf ! Visiblement, MySQL vérifie la contrainte de clé étrangère lors d'une suppression aussi, et empêche de supprimer une ligne
si elle contient une référence utilisée ailleurs dans la base (ici, l'id de la ligne est donc utilisé par certaines lignes de la table
Animal).
Partie 2 : Index, jointures et sous-requêtes
136/414
www.openclassrooms.com
Lorsque je vous ai parlé des clés étrangères, et que je vous ai donné la syntaxe pour les créer, j'ai omis de vous parler des deux
options fort utiles :
ON DELETE, qui permet de déterminer le comportement de MySQL en cas de suppression d'une référence ;
ON UPDATE, qui permet de déterminer le comportement de MySQL en cas de modification d'une référence.
Nous allons maintenant examiner ces options.
Option sur suppression des clés étrangères
Petits rappels
La syntaxe
Voici comment on ajoute une clé étrangère à une table déjà existante :
Code : SQL
ALTER TABLE nom_table
ADD [CONSTRAINT fk_col_ref]
-- On donne un nom à la clé
(facultatif)
FOREIGN KEY colonne
-- La colonne sur laquelle on
ajoute la clé
REFERENCES table_ref(col_ref); -- La table et la colonne de
référence
Le principe expliqué ici est exactement le même si l'on crée la clé en même temps que la table. La commande ALTER
TABLE est simplement plus courte, c'est la raison pour laquelle je l'utilise dans mes exemples plutôt que CREATE
TABLE.
Le principe
Dans notre table Animal, nous avons par exemple mis une clé étrangère sur la colonne race_id , référençant la colonne id de la
table Race. Cela implique que chaque fois qu'une valeur est insérée dans cette colonne (soit en ajoutant une ligne, soit en
modifiant une ligne existante), MySQL va vérifier que cette valeur existe bien dans la colonne id de la table Race.
Aucun animal ne pourra donc avoir un race_id qui ne correspond à rien dans notre base.
Suppression d'une référence
Que se passe-t-il si l'on supprime la race des Boxers ? Certains animaux référencent cette espèce dans leur colonne race_id . On
risque donc d'avoir des données incohérentes. Or, éviter cela est précisément la raison d'être de notre clé étrangère.
Essayons :
Code : SQL
DELETE FROM Race WHERE nom = 'Boxer';
Code : Console
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Ouf ! Visiblement, MySQL vérifie la contrainte de clé étrangère lors d'une suppression aussi, et empêche de supprimer une ligne
si elle contient une référence utilisée ailleurs dans la base (ici, l'id de la ligne est donc utilisé par certaines lignes de la table
Animal).
Partie 2 : Index, jointures et sous-requêtes
136/414
www.openclassrooms.com