Contraintes d'intégrité - je ne comprends pas les différences

Bonjour à tous,

J'ai beau chercher et lire les explications sur les Contraintes d'intégrité référentielles lors de la création des clés étrangères j'ai du mal à comprendre ce qui est écrit.

J'ai créé ma base sur MYSQL en innoDB. Pour le moment j'ai fais toutes mes liaisons de clés étrangères et mes tables associatives sur la papier pour bien comprendre et ne rien oublier. J'en suis donc à la traduction de ce que j'ai écrit sur MYSQL qui tourne sur wampserver.

J'ai donc commencé par le plus simple: ajouter une clé étrangère à une table. J'ai nommé le nom de la contrainte et là je bloque sur les ON DELETE et ON UPDATE. J'ai donc lu ce qui était écrit sur les effets de ces contraintes mais je n'ai pas trop compris leurs effets et je ne sais donc pas trop quoi appliquer pour que cela corresponde à mes besoins.

Pour faire simple, j'ai deux tables: chaine et serie.
Une chaine produit de 0 à n série, donc le clé étrangère est sur chaine.
Dans ce que j'ai compris, il faudrait que je mette :

• ON DELETE : SET NULL
• ON UPDATE : SET NULL

Car je peux avoir une série produite sans chaine de télévision et inversement. Et si tous les éléments sont créés je peux en supprimer un des deux sans que l'autre soit affecté.

Est-ce que j'ai bien compris?

Je vous remercie d'avance pour vos éclaircissements.

Comment expliquer la chose simplement ?

Je ne connais pas particulièrement MySQL, mais le comportement est similaire dans tout SGBD un tant soit peu sérieux
Les clauses ON DELETE et ON UPDATE n'interviennent pas directement sur la relation que l'on peut mettre entre 2 tables. Elles n'interviennent que dans un cas très particulier qui est la suppression/modification du lien parent (clé primaire) dans la relation déjà établie parent/enfant, et indique comment le lien enfant (clé étrangère) doit être modifié en conséquence.

Avec une telle clause, la clé étrangère est toujours modifiée (et si la modification est impossible, ça fait échouer la transaction).
avec un ON DELETE SET NULL, par exemple, lors de la suppression de l'enregistrement parent d'une relation, cette clause dit que toutes les clés étrangères qui référençaient ce parent sont modifiées pour être mises à NULL
avec un ON DELETE CASCADE, par exemple, lors de cette même suppression, la clause indique que l'on va aussi supprimer arbitrairement tous les enregistrements enfants qui référençaient cet enregistrement parent.

Mais en aucun cas on ne touche à des enregistrements enfant qui référencent, eux, un autre parent

Dans ton exemple, c'est la table série qui référence via une clé étrangère la table chaine. Les clauses ON DELETE et ON UPDATE n'interviendront que lorsque tu modifiera/supprimera une chaine de télévision, et seules les séries qui référençaient cette chaine seront affectées en voyant leur clé étrangère passer à NULL

Bonjour sevyc64,

Merci pour ta réponse.

Donc si je comprends bien, si je met le ON DELETE sur SET NULL, quand, admettons, je supprime la chaine de TV M6, le lien de clé étrangère ne sera pas supprimé mais mis en tant que null.
Et si le ON DELETE est sur CASCADE quand je supprime la chaine M6, ses séries seront-elles aussi supprimées ?
Seules les informations « clés » étrangères sont touchées ? Pas les informations des autres tables ?

Ce que je crains c’est que si je dois supprimer un enregistrement qui est faux, par exemple un doublon au niveau des chaines de TV. Je ne veux pas que les séries qui étaient liées au doublon soient supprimées aussi.

Si tu met sur SET NULL, à la supression de la chaine, la clé étrangère de la série sera mise à NULL, la série se retrouvera sans chaine attribuée au lieu d'être orpheline (avec une chaine attribuée n'existant plus) mais existera toujours, indépendante.

Si tu met sur CASCADE, à la suppression de la chaine, la série (tout l'enregistrement et pas seulement la clé étrangère) sera aussi supprimée.

L'exemple banal que l'on pourrait prendre est une table Bon de commande, et une table Ligne ayant pour clé étrangère l'id du bon de commande. Ici la clé étrangère sera positionnée sur ON DELETE CASCADE car si on supprime le bon de commande, il n'y a pas lieu de conserver les lignes puisqu'elles ne peuvent exister que pour celui-ci et non pas vocation à pouvoir exister seules. Donc à la suppression du bon de commande, les lignes du bon de commande sont aussi supprimées en cascade.

Mais par contre, ces lignes référençant un produit, clé étrangère aussi, ici elle sera positionnée sur NO ACTION (qui doit normalement être l'option par défaut). Ainsi il ne sera pas possible de supprimer un produit (le parent) tant qu'au moins une ligne (l'enfant) de bon de commande existe en base référençant ce parent.

Donc en gros :
SET NULL : on peut supprimer le parent, les enfants ne sont pas supprimés mais deviennent indépendants
CASCADE : on peut supprimer le parent, les enfants sont aussi automatiquement supprimés
NO ACTION : on ne peut pas supprimer le parent tant qu'il existe quelque part au moins un enfant

Ahhh d'accord j'ai sisi!

Merci pour cette explication et ta patience.

je prend note de ton explication pour l'intégrer à mes données. :D

Dans mon cas ce sera donc du Set Nul. ;)


Bon dimanche.