J'ai deux tables dans la base de données MySQL - parent
, child
. J'essaie d'ajouter des références de clés étrangères à ma table enfant en fonction de la table parent. Y a-t-il une différence significative entre ON UPDATE CASCADE
et ON DELETE CASCADE
Ma table des parents
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
Ma question est: Quelle est la différence entre les requêtes SQL suivantes.
ON DELETE CASCADE
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
ON UPDATE CASCADE
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE CASCADE
) ENGINE=INNODB;
ON UPDATE CASCADE ON DELETE CASCADE
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;
Y a-t-il des erreurs dans les requêtes? Que signifient ces requêtes (1, 2 et 3)? Sont-ils les mêmes ???
Dans la norme SQL 2003, il existe 5 actions référentielles différentes:
Pour répondre à la question:
[~ # ~] cascade [~ # ~]
ON DELETE CASCADE
signifie que si l'enregistrement parent est supprimé, tous les enregistrements enfants sont également supprimés. Ce n'est pas une bonne idée à mon avis. Vous devez garder une trace de toutes les données qui ont déjà été dans une base de données, bien que cela puisse être fait en utilisant TRIGGER
s. (Cependant, voir la mise en garde dans les commentaires ci-dessous).
ON UPDATE CASCADE
signifie que si la clé primaire parent est modifiée, la valeur enfant changera également pour refléter cela. Encore une fois à mon avis, ce n'est pas une bonne idée. Si vous changez PRIMARY KEY
s avec n'importe quelle régularité (ou même pas du tout!), il y a quelque chose qui ne va pas avec votre design. Encore une fois, voir les commentaires.
ON UPDATE CASCADE ON DELETE CASCADE
signifie que si vous UPDATE
[~ # ~] ou [~ # ~] DELETE
le parent, le changement est répercuté sur l'enfant. C'est l'équivalent de AND
ing les résultats des deux premières instructions.
[~ # ~] restreindre [~ # ~]
RESTRICT
signifie que toute tentative de suppression et/ou de mise à jour du parent échouera à générer une erreur. Il s'agit du comportement par défaut dans le cas où une action référentielle n'est pas explicitement spécifiée. Pour un
ON DELETE
ouON UPDATE
non spécifié, l'action par défaut est toujours RESTRICT`.
AUCUNE ACTION
NO ACTION
: Dans le manuel . Un mot-clé de SQL standard. Dans MySQL, équivalent à RESTRICT
. Le serveur MySQL rejette l'opération de suppression ou de mise à jour de la table parent s'il existe une valeur de clé étrangère associée dans la table référencée. Certains systèmes de base de données ont des vérifications différées et NO ACTION
est un chèque différé. Dans MySQL, les contraintes de clé étrangère sont vérifiées immédiatement, donc NO ACTION
est identique à RESTRICT
.SET NULL
SET NULL
- encore une fois dans le manuel. Supprimez ou mettez à jour la ligne de la table parent et définissez la ou les colonnes de clé étrangère de la table enfant sur NULL
. Ce n'est pas la meilleure des idées à mon humble avis, principalement parce qu'il n'y a aucun moyen de "voyager dans le temps" - c'est-à-dire de regarder en arrière dans les tables enfants et d'associer des enregistrements avec NULL
s à l'enregistrement parent correspondant - soit CASCADE
ou utilisez TRIGGER
s pour remplir les tables de journalisation afin de suivre les modifications (mais, voir les commentaires).SET DEFAULT
SET DEFAULT
. Encore une autre partie (potentiellement très utile) du standard SQL que MySQL n'a pas pris la peine de mettre en œuvre! Permet au développeur de spécifier une valeur à laquelle définir la ou les colonnes de clé étrangère sur une MISE À JOUR ou une SUPPRESSION. InnoDB et NDB rejetteront les définitions de table avec un SET DEFAULT
clause.Comme mentionné ci-dessus, vous devriez passer du temps à regarder la documentation, ici .
Ces deux actions sont à effectuer, respectivement, lorsque l'enregistrement référencé sur la table parent change son identifiant et lorsqu'il est supprimé.
Si vous exécutez:
UPDATE parent SET id = -1 WHERE id = 1;
Et il y a au moins un enregistrement sur child
avec parent_id = 1
, 1) échouera; dans les cas 2) et 3), tous les enregistrements avec parent_id = 1 sont mis à jour en parent_id = -1.
Si vous exécutez:
DELETE FROM parent WHERE id = 1;
Et il y a au moins un enregistrement sur child
avec parent_id = 1
, 2) échouera; dans les cas 1) et 3), tous les enregistrements avec parent_id = 1
sont supprimés.
3) est syntaxiquement correcte.
Documentation complète se trouve dans le manuel .
Je n'ai pas assez de réputation pour commenter les réponses précédentes. J'ai donc pensé développer un peu.
1) ON DELETE CASCADE signifie que si l'enregistrement parent est supprimé, tous les enregistrements enfants référencés sont également supprimés. ON UPDATE est par défaut RESTRICT, ce qui signifie que la mise à jour sur l'enregistrement parent échouera.
2) L'action ON DELETE par défaut est RESTRICT, ce qui signifie que la suppression de l'enregistrement parent échouera. ON UPDATE CASCADE mettra à jour tous les enregistrements enfants référençant lorsque l'enregistrement parent est mis à jour.
3) Voir les actions CASCADE en 1) et 2) ci-dessus.
Sur l'utilisation des ID d'enregistrement parent comme clés étrangères (dans les tables enfants) - l'expérience dit a) si les ID sont des numéros de séquence générés automatiquement, NE LES UTILISEZ PAS comme clés étrangères. Utilisez à la place une autre clé parent unique. b) si les identifiants sont des GUID, il est alors possible de les utiliser comme clés étrangères. Vous verrez la sagesse de cette suggestion lorsque vous exportez et importez les enregistrements ou copiez des enregistrements dans une autre base de données. Il est trop lourd de gérer les numéros de séquence générés automatiquement lors de la migration des données lorsqu'ils sont référencés en tant que clés étrangères.