Mais ça veut donc dire que chaque fois qu'on veut supprimer des lignes de la table Race, il faut d'abord supprimer toutes les
références à ces races. Dans notre base, ça va encore, il n'y a pas énormément de clés étrangères, mais imaginez si l'id de Race
servait de référence à des clés étrangères dans ne serait-ce que cinq ou six tables. Pour supprimer une seule race, il faudrait faire
jusqu'à six ou sept requêtes.
C'est donc ici qu'intervient notre option ON DELETE, qui permet de changer la manière dont la clé étrangère gère la suppression
d'une référence.
Syntaxe
Voici comment on ajoute cette option à la clé étrangère :
Code : SQL
ALTER TABLE nom_table
ADD [CONSTRAINT fk_col_ref]
FOREIGN KEY (colonne)
REFERENCES table_ref(col_ref)
ON DELETE {RESTRICT | NO ACTION | SET NULL | CASCADE}; -- <-Nouvelle option !
Il y a donc quatre comportements possibles, que je vais vous détailler tout de suite (bien que leurs noms soient plutôt clairs) :
RESTRICT, NO ACTION, SET NULL et CASCADE.
RESTRICT ou NO ACTION
RESTRICT est le comportement par défaut. Si l'on essaye de supprimer une valeur référencée par une clé étrangère, l'action est
avortée et on obtient une erreur. NO ACTION a exactement le même effet.
Cette équivalence de RESTRICT et NO ACTION est propre à MySQL. Dans d'autres SGBD, ces deux options n'auront
pas le même effet (RESTRICT étant généralement plus strict que NO ACTION).
SET NULL
Si on choisit SET NULL, alors tout simplement, NULL est substitué aux valeurs dont la référence est supprimée.
Pour reprendre notre exemple, en supprimant la race des Boxers, tous les animaux auxquels on a attribué cette race verront la
valeur de leur race_id passer à NULL.
D'ailleurs, ça me semble plutôt intéressant comme comportement dans cette situation ! Nous allons donc modifier notre clé
étrangère fk_race_id . C'est-à-dire que nous allons supprimer la clé, puis la recréer avec le bon comportement :
Code : SQL
ALTER TABLE Animal DROP FOREIGN KEY fk_race_id;
ALTER TABLE Animal
ADD CONSTRAINT fk_race_id FOREIGN KEY (race_id) REFERENCES Race(id)
ON DELETE SET NULL;
Dorénavant, si vous supprimez une race, tous les animaux auxquels vous avez attribué cette race auparavant auront NULL
comme race_id .
Vérifions en supprimant les Boxers, depuis le temps qu'on essaye !
Code : SQL
-- Affichons d'abord tous les animaux, avec leur race --- ----------------------------------------------------SELECT Animal.nom, Animal.race_id, Race.nom as race FROM Animal
LEFT JOIN Race ON Animal.race_id = Race.id
Partie 2 : Index, jointures et sous-requêtes
137/414
www.openclassrooms.com
références à ces races. Dans notre base, ça va encore, il n'y a pas énormément de clés étrangères, mais imaginez si l'id de Race
servait de référence à des clés étrangères dans ne serait-ce que cinq ou six tables. Pour supprimer une seule race, il faudrait faire
jusqu'à six ou sept requêtes.
C'est donc ici qu'intervient notre option ON DELETE, qui permet de changer la manière dont la clé étrangère gère la suppression
d'une référence.
Syntaxe
Voici comment on ajoute cette option à la clé étrangère :
Code : SQL
ALTER TABLE nom_table
ADD [CONSTRAINT fk_col_ref]
FOREIGN KEY (colonne)
REFERENCES table_ref(col_ref)
ON DELETE {RESTRICT | NO ACTION | SET NULL | CASCADE}; -- <-Nouvelle option !
Il y a donc quatre comportements possibles, que je vais vous détailler tout de suite (bien que leurs noms soient plutôt clairs) :
RESTRICT, NO ACTION, SET NULL et CASCADE.
RESTRICT ou NO ACTION
RESTRICT est le comportement par défaut. Si l'on essaye de supprimer une valeur référencée par une clé étrangère, l'action est
avortée et on obtient une erreur. NO ACTION a exactement le même effet.
Cette équivalence de RESTRICT et NO ACTION est propre à MySQL. Dans d'autres SGBD, ces deux options n'auront
pas le même effet (RESTRICT étant généralement plus strict que NO ACTION).
SET NULL
Si on choisit SET NULL, alors tout simplement, NULL est substitué aux valeurs dont la référence est supprimée.
Pour reprendre notre exemple, en supprimant la race des Boxers, tous les animaux auxquels on a attribué cette race verront la
valeur de leur race_id passer à NULL.
D'ailleurs, ça me semble plutôt intéressant comme comportement dans cette situation ! Nous allons donc modifier notre clé
étrangère fk_race_id . C'est-à-dire que nous allons supprimer la clé, puis la recréer avec le bon comportement :
Code : SQL
ALTER TABLE Animal DROP FOREIGN KEY fk_race_id;
ALTER TABLE Animal
ADD CONSTRAINT fk_race_id FOREIGN KEY (race_id) REFERENCES Race(id)
ON DELETE SET NULL;
Dorénavant, si vous supprimez une race, tous les animaux auxquels vous avez attribué cette race auparavant auront NULL
comme race_id .
Vérifions en supprimant les Boxers, depuis le temps qu'on essaye !
Code : SQL
-- Affichons d'abord tous les animaux, avec leur race --- ----------------------------------------------------SELECT Animal.nom, Animal.race_id, Race.nom as race FROM Animal
LEFT JOIN Race ON Animal.race_id = Race.id
Partie 2 : Index, jointures et sous-requêtes
137/414
www.openclassrooms.com