ORDER BY race;
-- Supprimons ensuite la race 'Boxer' --- ------------------------------------DELETE FROM Race WHERE nom = 'Boxer';
-- Réaffichons les animaux --- -------------------------SELECT Animal.nom, Animal.race_id, Race.nom as race FROM Animal
LEFT JOIN Race ON Animal.race_id = Race.id
ORDER BY race;
Les ex-boxers existent toujours dans la table Animal, mais ils n'appartiennent plus à aucune race.
CASCADE
Ce dernier comportement est le plus risqué (et le plus violent !
). En effet, cela supprime purement et simplement toutes les
lignes qui référençaient la valeur supprimée !
Donc, si on choisit ce comportement pour la clé étrangère sur la colonne espece_id de la table Animal, vous supprimez l'espèce
"Perroquet amazone" et POUF !, quatre lignes de votre table Animal (les quatre perroquets) sont supprimées en même temps.
Il faut donc être bien sûr de ce que l'on fait si l'on choisit ON DELETE CASCADE. Il y a cependant de nombreuses situations
dans lesquelles c'est utile. Prenez par exemple un forum sur un site internet. Vous avez une table Sujet, et une table Message,
avec une colonne sujet_id . Avec ON DELETE CASCADE, il vous suffit de supprimer un sujet pour que tous les messages de
ce sujet soient également supprimés. Plutôt pratique non ?
Je le répète : soyez bien sûrs de ce que vous faites ! Je décline toute responsabilité en cas de perte de données causée
par un ON DELETE CASCADE inconsidérément utilisé !
Option sur modification des clés étrangères
On peut également rencontrer des problèmes de cohérence des données en cas de modification. En effet, si l'on change par
exemple l'id de la race "Singapura", tous les animaux qui ont l'ancien id dans leur colonne race_id référenceront une ligne qui
n'existe plus. Les modifications de références de clés étrangères sont donc soumises aux mêmes restrictions que la suppression.
Exemple : essayons de modifier l'id de la race "Singapura".
Code : SQL
UPDATE Race SET id = 3 WHERE nom = 'Singapura';
Code : Console
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
L'option permettant de définir le comportement en cas de modification est donc ON UPDATE {RESTRICT | NO ACTION
| SET NULL | CASCADE}. Les quatre comportements possibles sont exactement les mêmes que pour la suppression.
RESTRICT et NO ACTION : empêche la modification si elle casse la contrainte (comportement par défaut).
SET NULL : met NULL partout où la valeur modifiée était référencée.
CASCADE : modifie également la valeur là où elle est référencée.
Petite explication à propos de CASCADE
CASCADE signifie que l'événement est répété sur les tables qui référencent la valeur. Pensez à des "réactions en cascade". Ainsi,
une suppression provoquera d'autres suppressions, tandis qu'une modification provoquera d'autres… modifications !
Modifions par exemple la clé étrangère sur Animal.race_id , avant de modifier l'id de la race "Singapura" (jetez d'abord un œil aux
données des tables Race et Animal, afin de voir les différences).
Partie 2 : Index, jointures et sous-requêtes
138/414
www.openclassrooms.com
-- Supprimons ensuite la race 'Boxer' --- ------------------------------------DELETE FROM Race WHERE nom = 'Boxer';
-- Réaffichons les animaux --- -------------------------SELECT Animal.nom, Animal.race_id, Race.nom as race FROM Animal
LEFT JOIN Race ON Animal.race_id = Race.id
ORDER BY race;
Les ex-boxers existent toujours dans la table Animal, mais ils n'appartiennent plus à aucune race.
CASCADE
Ce dernier comportement est le plus risqué (et le plus violent !
). En effet, cela supprime purement et simplement toutes les
lignes qui référençaient la valeur supprimée !
Donc, si on choisit ce comportement pour la clé étrangère sur la colonne espece_id de la table Animal, vous supprimez l'espèce
"Perroquet amazone" et POUF !, quatre lignes de votre table Animal (les quatre perroquets) sont supprimées en même temps.
Il faut donc être bien sûr de ce que l'on fait si l'on choisit ON DELETE CASCADE. Il y a cependant de nombreuses situations
dans lesquelles c'est utile. Prenez par exemple un forum sur un site internet. Vous avez une table Sujet, et une table Message,
avec une colonne sujet_id . Avec ON DELETE CASCADE, il vous suffit de supprimer un sujet pour que tous les messages de
ce sujet soient également supprimés. Plutôt pratique non ?
Je le répète : soyez bien sûrs de ce que vous faites ! Je décline toute responsabilité en cas de perte de données causée
par un ON DELETE CASCADE inconsidérément utilisé !
Option sur modification des clés étrangères
On peut également rencontrer des problèmes de cohérence des données en cas de modification. En effet, si l'on change par
exemple l'id de la race "Singapura", tous les animaux qui ont l'ancien id dans leur colonne race_id référenceront une ligne qui
n'existe plus. Les modifications de références de clés étrangères sont donc soumises aux mêmes restrictions que la suppression.
Exemple : essayons de modifier l'id de la race "Singapura".
Code : SQL
UPDATE Race SET id = 3 WHERE nom = 'Singapura';
Code : Console
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
L'option permettant de définir le comportement en cas de modification est donc ON UPDATE {RESTRICT | NO ACTION
| SET NULL | CASCADE}. Les quatre comportements possibles sont exactement les mêmes que pour la suppression.
RESTRICT et NO ACTION : empêche la modification si elle casse la contrainte (comportement par défaut).
SET NULL : met NULL partout où la valeur modifiée était référencée.
CASCADE : modifie également la valeur là où elle est référencée.
Petite explication à propos de CASCADE
CASCADE signifie que l'événement est répété sur les tables qui référencent la valeur. Pensez à des "réactions en cascade". Ainsi,
une suppression provoquera d'autres suppressions, tandis qu'une modification provoquera d'autres… modifications !
Modifions par exemple la clé étrangère sur Animal.race_id , avant de modifier l'id de la race "Singapura" (jetez d'abord un œil aux
données des tables Race et Animal, afin de voir les différences).
Partie 2 : Index, jointures et sous-requêtes
138/414
www.openclassrooms.com