Options des clés étrangères

• Union de plusieurs requêtes
• Sommaire
• Violation de contrainte d'unicité

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
• Option sur modification des clés étrangères
• Utilisation de ces options dans notre base

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 :

1
2
3
4

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

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 :

1

DELETE FROM Race WHERE nom = 'Boxer';

1

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`elevage`.`animal`, CONSTRAINT `fk_race_id` FOREIGN KEY (`race_id`) REFERENCES `race` (`id`))

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).

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 :

1
2
3
4
5

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.

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 :

1
2
3
4

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 !

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 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
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 ?

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".

1

UPDATE Race SET id = 3 WHERE nom = 'Singapura';

1

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`elevage`.`animal`, CONSTRAINT `fk_race_id` FOREIGN KEY (`race_id`) REFERENCES `race` (`id`) ON DELETE SET NULL)

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).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- Suppression de la clé --
-- ------------------------
ALTER TABLE Animal DROP FOREIGN KEY fk_race_id;

-- Recréation de la clé avec les bonnes options --
-- -----------------------------------------------
ALTER TABLE Animal
ADD CONSTRAINT fk_race_id FOREIGN KEY (race_id) REFERENCES Race(id)
    ON DELETE SET NULL   -- N'oublions pas de remettre le ON DELETE !
    ON UPDATE CASCADE;   

-- Modification de l'id des Singapura --
-- -------------------------------------
UPDATE Race SET id = 3 WHERE nom = 'Singapura';

Les animaux notés comme étant des "Singapura" ont désormais leur race_id à 3. Parfait !

Modifions une dernière fois cette clé étrangère pour remettre l'option ON UPDATE par défaut.

1
2
3
4

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;

Utilisation de ces options dans notre base

Appliquons maintenant ce que nous avons appris à notre base de données elevage. Celle-ci comporte 5 clés étrangères :

Sur la table Animal

• race_id référence Race.id
• espece_id référence Espece.id
• mere_id référence Animal.id
• pere_id référence Animal.id

Sur la table Race

• espece_id référence Espece.id

Modifications

Pour les modifications, le mieux ici est de laisser le comportement par défaut (RESTRICT) pour toutes nos clés étrangères. Les colonnes référencées sont chaque fois des colonnes auto-incrémentées, on ne devrait donc pas modifier leurs valeurs.

Suppressions

Le problème est plus délicat pour les suppressions. On a déjà défini ON DELETE SET NULL pour la clé sur Animal.race_id. Prenons les autres clés une à une.

Clé sur Animal.espece_id

Si l'on supprime une espèce de la base de données, c'est qu'on ne l'utilise plus dans notre élevage, donc a priori, on n'a plus besoin non plus des animaux de cette espèce. Sachant cela, on serait sans doute tentés de mettre ON DELETE CASCADE. Ainsi, en une seule requête, tout est fait. Cependant, les animaux sont quand même le point central de notre base de données. Cela me paraît donc un peu violent de les supprimer automatiquement de cette manière, en cas de suppression d'espèce. Par conséquent, je vous propose plutôt de laisser le ON DELETE RESTRICT. Supprimer une espèce n'est pas anodin, et supprimer de nombreux animaux d'un coup non plus. En empêchant la suppression des espèces tant qu'il existe des animaux de celle-ci, on oblige l'utilisateur à supprimer d'abord tous ces animaux. Pas de risque de fausse manœuvre donc. Attention au fait que le ON DELETE SET NULL n'est bien sûr pas envisageable, puisque la colonne espece_id de la table Animal ne peut pas être NULL. Pas de changement pour cette clé étrangère !

Clés sur Animal.mere_id et Animal.pere_id

Ce n'est pas parce qu'on supprime un animal que tous ses enfants doivent être supprimés également. Par contre, mettre à NULL semble une bonne idée. ON DELETE SET NULL donc !

Clé sur Race.espece_id

Si une espèce est finalement supprimée, et donc que tous les animaux de cette espèce ont également été supprimés auparavant (puisqu'on a laissé ON DELETE RESTRICT pour la clé sur Animal.espece_id), alors les races de cette espèce deviennent caduques. On peut donc utiliser un ON DELETE CASCADE ici.

Les requêtes

Vous avez toutes les informations nécessaires pour écrire ces requêtes, je vous encourage donc à les écrire vous-mêmes avant de regarder mon code.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- Animal.mere_id --
-- -----------------
ALTER TABLE Animal DROP FOREIGN KEY fk_mere_id;

ALTER TABLE Animal
ADD CONSTRAINT fk_mere_id FOREIGN KEY (mere_id) REFERENCES Animal(id) ON DELETE SET NULL;

-- Animal.pere_id --
-- -----------------
ALTER TABLE Animal DROP FOREIGN KEY fk_pere_id;

ALTER TABLE Animal
ADD CONSTRAINT fk_pere_id FOREIGN KEY (pere_id) REFERENCES Animal(id) ON DELETE SET NULL;

-- Race.espece_id --
-- -----------------
ALTER TABLE Race DROP FOREIGN KEY fk_race_espece_id;

ALTER TABLE Race
ADD CONSTRAINT fk_race_espece_id FOREIGN KEY (espece_id) REFERENCES Espece(id) ON DELETE CASCADE;

En résumé

• Lorsque l'on crée (ou modifie) une clé étrangère, on peut lui définir deux options : ON DELETE, qui sert en cas de suppression de la référence ; et ON UPDATE, qui sert en cas de modification de la référence.
• RESTRICT et NO ACTION désignent le comportement par défaut : la référence ne peut être ni supprimée, ni modifiée si cela entraîne des données incohérentes vis-à-vis de la clé étrangère.
• SET NULL fait en sorte que les données de la clé étrangère ayant perdu leur référence (suite à une modification ou une suppression) soient mises à NULL.
• CASCADE répercute la modification ou la suppression d'une référence de clé étrangère sur les lignes impactées.

• Union de plusieurs requêtes
• Sommaire
• Violation de contrainte d'unicité