Contraintes de clé étrangère : Quand utiliser ON UPDATE et ON DELETE ?

Question

Je conçois mon schéma de base de données à l'aide de MySQL Workbench, qui est assez cool parce que vous pouvez faire des diagrammes et il les convertit :P

Quoi qu'il en soit, j'ai décidé d'utiliser InnoDB en raison de son support des clés étrangères. Une chose que j'ai remarquée cependant, c'est qu'elle permet de définir des options On Update et On Delete pour les clés étrangères. Quelqu'un peut-il m'expliquer où les options "Restrict", "Cascade" et set null peuvent être utilisées dans un exemple simple ?

Par exemple, disons que j'ai un user qui comprend un userID . Et dire que j'ai un tableau de messages message qui est un many-to-many qui a 2 clés étrangères (qui font référence à la même clé primaire), userID en el user table). La définition des options On Update et On Delete est-elle utile dans ce cas ? Si oui, laquelle dois-je choisir ? Si ce n'est pas un bon exemple, pourriez-vous proposer un bon exemple pour illustrer l'utilité de ces options ?

Gracias

Answer 1

N'hésitez pas à mettre des contraintes sur la base de données. Vous serez sûr d'avoir une base de données cohérente, et c'est l'une des bonnes raisons d'utiliser une base de données. Surtout si vous avez plusieurs applications qui la sollicitent (ou une seule application mais avec un mode direct et un mode batch utilisant des sources différentes).

Avec MySQL, vous ne disposez pas de contraintes avancées comme avec PostgreSQL, mais au moins les contraintes de clé étrangère sont assez avancées.

Nous allons prendre un exemple, une table de société avec une table d'utilisateurs contenant les personnes de cette société.

CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT, 
     user_name VARCHAR(50), 
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;

Regardons les ON UPDATE clause :

• SUR LA MISE À JOUR RESTREINTE : le défaut Si vous essayez de mettre à jour un numéro de société dans la table COMPANY, le moteur rejettera l'opération si au moins un USER est lié à cette société.
• SUR LA MISE À JOUR AUCUNE ACTION : même chose que RESTRICT.
• SUR LA CASCADE DE MISE À JOUR : le meilleur d'habitude Si vous mettez à jour un company_id dans une ligne de la table COMPANY, le moteur le mettra à jour en conséquence sur toutes les lignes de USER faisant référence à cette COMPANY (mais pas de triggers activés sur la table USER, attention). Le moteur suivra les changements pour vous, c'est bien.
• EN CAS DE MISE À JOUR, METTRE NULL Si vous mettez à jour un numéro de société dans une ligne de la table COMPANY, le moteur mettra à NULL le numéro de société de l'utilisateur concerné (qui devrait être disponible dans le champ numéro de société de l'utilisateur). Je ne vois rien d'intéressant à faire avec cela lors d'une mise à jour, mais je peux me tromper.

Et maintenant sur le ON DELETE côté :

• SUR SUPPRIMER RESTREINDRE : le défaut : si vous essayez de supprimer un identifiant de société dans la table COMPANY le moteur rejettera l'opération si un USER au moins est lié à cette société, cela peut vous sauver la vie.
• SUR LA SUPPRESSION AUCUNE ACTION : identique à RESTRICT
• SUR LA SUPPRESSION EN CASCADE : dangereux Si vous supprimez une ligne de société dans la table COMPANY, le moteur supprimera également les USERs associés. Ceci est dangereux mais peut être utilisé pour faire des nettoyages automatiques sur des tables secondaires (donc cela peut être quelque chose que vous voulez, mais certainement pas pour un exemple COMPANY<->USER).
• EN CAS DE SUPPRESSION, METTRE NULL : poignée de Si vous supprimez une ligne COMPANY, les USER associés auront automatiquement la relation avec NULL. Si Null est votre valeur pour les utilisateurs sans société, cela peut être un bon comportement, par exemple vous avez peut-être besoin de garder les utilisateurs dans votre application, comme auteurs de certains contenus, mais la suppression de la société n'est pas un problème pour vous.

habituellement mon défaut est : SUR LA SUPPRESSION RESTREINTE SUR LA MISE À JOUR EN CASCADE . avec quelques ON DELETE CASCADE pour les tableaux de suivi (journaux - pas tous les journaux -, des choses comme ça) et ON DELETE SET NULL lorsque la table maîtresse est un "simple attribut" de la table contenant la clé étrangère, comme une table JOB pour la table USER.

Editar

Cela fait longtemps que je n'ai pas écrit ça. Maintenant, je pense que je dois ajouter un avertissement important. MySQL a une limitation importante documentée avec les cascades. Les cascades ne sont pas des déclencheurs de tir . Donc, si vous étiez assez confiant dans ce moteur pour utiliser des triggers, vous devriez éviter les contraintes en cascade.

• http://dev.mysql.com/doc/refman/5.6/en/triggers.html

Les déclencheurs MySQL ne s'activent que pour les changements apportés aux tables par les instructions SQL. Ils ne s'activent pas pour les changements dans les vues, ni pour les changements apportés aux tables par des API qui ne transmettent pas d'instructions SQL au serveur MySQL.

• http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

\==> Voir ci-dessous la dernière modification, les choses bougent sur ce domaine.

Les déclencheurs ne sont pas activés par des actions de clé étrangère.

Et je ne pense pas que cela sera réparé un jour. Les contraintes de clés étrangères sont gérées par le stockage InnoDb et les déclencheurs sont gérés par le moteur SQL de MySQL. Les deux sont séparés. Innodb est le seul stockage avec la gestion des contraintes, peut-être qu'ils ajouteront les triggers directement dans le moteur de stockage un jour, peut-être pas.

Mais j'ai ma propre opinion sur l'élément que vous devriez choisir entre la mauvaise implémentation des triggers et le support très utile des contraintes de clés étrangères. Et une fois que vous vous serez habitué à la cohérence des bases de données, vous adorerez PostgreSQL.

12/2017-Mise à jour de cette édition sur MySQL :

comme indiqué par @IstiaqueAhmed dans les commentaires, la situation a changé à ce sujet. Suivez donc le lien et vérifiez la situation réelle et actualisée (qui peut encore changer à l'avenir).

Answer 2

Vous devrez considérer cela dans le contexte de la demande. En général, vous devez concevoir une application, et non une base de données (la base de données faisant simplement partie de l'application).

Réfléchissez à la manière dont votre application doit répondre à différents cas.

L'action par défaut est de restreindre (c'est-à-dire de ne pas autoriser) l'opération, ce qui est normalement ce que vous voulez car cela évite des erreurs de programmation stupides. Cependant, sur DELETE CASCADE peut également être utile. Cela dépend vraiment de votre application et de la manière dont vous avez l'intention de supprimer des objets particuliers.

Personnellement, j'utiliserais InnoDB parce qu'il ne détruit pas vos données (contrairement à MyISAM, qui le fait), plutôt que parce qu'il a des contraintes FK.

Answer 3

Ajout à la réponse de @MarkR - une chose à noter est que de nombreux frameworks PHP avec ORM ne reconnaissent pas ou n'utilisent pas la configuration avancée de la base de données (clés étrangères, suppression en cascade, contraintes uniques), ce qui peut entraîner un comportement inattendu.

Par exemple, si vous supprimez un enregistrement en utilisant ORM, et que votre DELETE CASCADE supprimera des enregistrements dans des tables liées, la tentative de l'ORM de supprimer ces enregistrements liés (souvent automatique) résultera en une erreur.