dbnewz

Pour assurer l’intégrité référentielle entre 2 tables, on crée une clé étrangère. Actuellement avec MySQL, InnoDB et PBXT sont capables de gérer ces clés étrangères. Jusque là, rien de bien nouveau… Très souvent, on ajoute dans la définition de la clé étrangère l’instruction ON DELETE CASCADE ON UPDATE CASCADE de manière à ce qu’une mise à jour ou une suppression dans la table parente soit impactée dans la table enfant. Il existe pourtant d’autres actions, c’est ce que je vous propose de découvrir (ou de redécouvrir) dans cet article.

Pour plus de clarté, nous allons nous intéresser à 2 tables dont le schéma est le suivant :

mysql> CREATE TABLE parent (
id INT NOT NULL AUTO_INCREMENT,
data varchar(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB;

et

mysql> CREATE TABLE enfant (
id int(11) NOT NULL AUTO_INCREMENT,
id_parent int(11) NOT NULL,
data2 varchar(20) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_parent FOREIGN KEY (id_parent) REFERENCES parent (id)
) ENGINE=InnoDB

Remplissons ces 2 tables avec quelques lignes pour faire nos tests :

mysql> INSERT INTO parent (data) VALUES ('test');
mysql> INSERT INTO enfant (id_parent,data2) VALUES (1,'test');
mysql> INSERT INTO enfant (id_parent,data2) VALUES (1,'test2');

Que se passe-t-il maintenant quand nous essayons de supprimer la ligne de la table parente ?

mysql> DELETE FROM parent WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`enfant`, CONSTRAINT `fk_parent` FOREIGN KEY (`id_parent`) REFERENCES `parent` (`id`))

Eh oui ! Quand on n’indique pas de clause particulière dans la définition de la clé étrangère, cela équivaut à indiquer la clause NO ACTION (ON DELETE NO ACTION ON UPDATE NO ACTION). Et cette action interdit la modification d’une ligne de la table parente si cette ligne est en liaison avec la table enfant par la clé étrangère. A noter : le mot-clé RESTRICT est équivalent à NO ACTION.

Testons maintenant le 3è mot-clé possible : SET NULL. Et pour cela, modifions la structure de la table enfant :

mysql> ALTER TABLE enfant DROP FOREIGN KEY fk_parent;
mysql> ALTER TABLE enfant ADD CONSTRAINT fk_parent FOREIGN KEY (id_parent) REFERENCES parent(id) ON DELETE SET NULL ON UPDATE SET NULL;
ERROR 1005 (HY000): Can't create table 'test.#sql-4fb_33' (errno: 150)

Ici MySQL ne permet pas la création de la clé étrangère. En effet, avec SET NULL, si la table parente est modifiée, les lignes liées par la clé étrangère sont mises à NULL dans la table enfant. Ceci ne fonctionne bien sûr que pour des colonnes qui peuvent être NULL, ce qui n’est pas le cas dans notre exemple. Vous pouvez vérifier le fonctionnement de SET NULL en ajoutant une nouvelle colonne autorisant les NULL dans nos 2 tables.

Enfin, pour terminer, la célèbre action CASCADE transmet la modification faite sur la table parent vers la table enfant :

mysql> ALTER TABLE enfant ADD CONSTRAINT fk_parent FOREIGN KEY (id_parent) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE;
mysql> SELECT * FROM enfant;


+----+-----------+-------+
| id | id_parent | data2 |
+----+-----------+-------+
|  1 |         1 | test  |
|  2 |         1 | test2 |
+----+-----------+-------+


mysql> UPDATE parent SET id = id +1;
mysql> SELECT * FROM enfant;


+----+-----------+-------+
| id | id_parent | data2 |
+----+-----------+-------+
|  1 |         2 | test  |
|  2 |         2 | test2 |
+----+-----------+-------+

Il est également possible d’avoir des actions différentes pour la suppression et pour la mise à jour (par exemple ON DELETE RESTRICT ON UPDATE CASCADE).

Il reste encore 2 petites précisions à apporter :

• certains SGBD (c’est le cas de PostgreSQL par exemple) font une différence entre RESTRICT et NO ACTION : RESTRICT vérifie immédiatement l’intégrité référentielle alors que NO ACTION vérifie l’intégrité à la fin de l’exécution de la requete, ce qui laisse le temps à d’éventuelles triggers d’être déclenchés et à la contrainte d’intégrité d’être finalement respectée.
• il existe dans la norme SQL une 5è action possible : SET DEFAULT, mais elle n’est pas supportée par MySQL

Mots-clefs : Clés étrangères, MySQL

Cet article a été publié le Mercredi 13 janvier 2010 à 6:25 et est classé dans MySQL. Vous pouvez en suivre les commentaires par le biais du flux RSS 2.0. Vous pouvez laisser un commentaire, ou faire un trackback depuis votre propre site.