mysql - foreign - Dans quel ordre les contraintes ON DELETE CASCADE sont-elles traitées?




on delete restrict (3)

Voici un exemple de ce que je fais:

CREATE TABLE Parent (id BIGINT NOT NULL,
  PRIMARY KEY (id)) ENGINE=InnoDB;

CREATE TABLE Child (id BIGINT NOT NULL,
  parentid BIGINT NOT NULL,
  PRIMARY KEY (id),
  KEY (parentid),
  CONSTRAINT fk_parent FOREIGN KEY (parentid) REFERENCES Parent (id) ON DELETE CASCADE) ENGINE=InnoDB;

CREATE TABLE Uncle (id BIGINT NOT NULL,
  parentid BIGINT NOT NULL,
  childid BIGINT NOT NULL,
  PRIMARY KEY (id),
  KEY (parentid),
  KEY (childid),
  CONSTRAINT fk_parent_u FOREIGN KEY (parentid) REFERENCES Parent (id) ON DELETE CASCADE,
  CONSTRAINT fk_child FOREIGN KEY (childid) REFERENCES Child (id)) ENGINE=InnoDB;

Notez qu'il n'y a pas de SUPPRIMER CASCADE pour la relation Oncle-Enfant; c'est-à-dire que la suppression d'un enfant ne supprime pas son oncle (s) et vice-versa.

Quand j'ai un Parent et un Oncle avec le même Enfant, et que je supprime le Parent, il semble qu'InnoDB devrait être capable de juste "comprendre" et laisser la cascade se répercuter dans toute la famille (ie supprimer le Parent supprime l'Oncle et l'enfant aussi). Cependant, à la place, je reçois ce qui suit:

  ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`cascade_test/uncle`, CONSTRAINT `fk_child` FOREIGN KEY (`childid`) REFERENCES `child` (`id`))

InnoDB tente de supprimer en cascade l'enfant avant que l'oncle (s) qui s'y réfèrent.

Est-ce que je manque quelque chose? Est-ce censé échouer pour une raison que je ne comprends pas? Ou y at-il un truc pour le faire fonctionner (ou est-ce un bug dans MySQL)?


Dans le cas le plus simple, que se passe-t-il si un enregistrement est supprimé de l'enfant et qu'il a un oncle de référence? C'est non spécifié, donc les contraintes échouent pour ça de toute façon.

Si la suppression d'un enfant ne supprime pas ses oncles, que se passe-t-il à la place? Uncle.childid ne peut pas être nul.

Ce que vous voulez est l'une de ces trois choses:

  1. Uncle.childid peut être nul, et vous voulez ON DELETE SET NULL pour childid.
  2. Uncle.childid ne peut pas être nul, et vous voulez ON DELETE CASCADE pour childid.
  3. Childid n'appartient pas à Oncle, et vous voulez une relation ChildsUncle avec les contraintes de clé étrangère ON DELETE CASCADE pour Child et Oncle. Uncleid serait une clé candidate pour cette relation (c'est-à-dire qu'elle devrait être unique).

le design est tout faux. Vous devriez avoir une seule table, avec une relation parent-enfant (littéralement). Ensuite, vous pouvez comprendre les oncles (et les tantes) avec une requête

select id from persons where -find all children of the grandparents
parent id in (
select parentid from persons --find the grandparents
where id in (
select parentid from persons --find the parents
where id=THECHILD) )
minus --and take out the child's parents
select parentid from persons
where id=THECHILD


@Matt Solnit tout d'abord c'est vraiment une bonne question et pour autant que je sache quand un enregistrement de Parent doit être supprimé, innodb essaye d'abord d'identifier quelles autres tables contiennent des références pour qu'il puisse effacer l'enregistrement de lui comme bien. Dans votre cas c'est la table enfant et la table Uncle, maintenant il semble que dans ce cas, il décide de supprimer d'abord la table enfant et donc répète le même processus pour Enfant et finit par échouer comme oncle détient la référence à la table enfant mais DELETE CASCADE "ou" ON DELETE SET NULL "est spécifié pour fk_child FK dans la table Uncle. Cependant, il semble que si innodb essaye d'abord de supprimer un enregistrement de la table Uncle, alors la suppression aurait du se faire en douceur. Bien après avoir réfléchi, je pense qu'innodb suit le modèle ACID et choisit Enfant sur Oncle pour commencer le processus de suppression parce qu'il commence avec Oncle même alors la suppression dans Child pourrait encore avoir échouée, par exemple supposer une table Friend qui a la clé fk_child (similaire à Oncle) sans ON DELETE CASCADE, maintenant cela aurait encore causé l'échec de toute la transaction et par conséquent cela me semble un bon comportement. En d'autres termes, innodb commence par une table qui peut provoquer un échec possible de la transaction, mais c'est en réalité ma théorie qui pourrait être une histoire complètement différente. :)





mysql-error-1451