处理ON DELETE CASCADE约束的顺序是什么？ [英] In what order are ON DELETE CASCADE constraints processed?

问题描述

下面是我所做的一个例子：

Here is an example of what I've got going on:

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;

注意，对于叔叔关系，没有ON DELETE CASCADE;即删除一个孩子不会删除其叔叔，反之亦然。

Notice there is no ON DELETE CASCADE for the Uncle-Child relationship; i.e. deleting a Child does not delete its Uncle(s) and vice-versa.

当我有同一个孩子的父母和叔叔，我删除父母就像InnoDB应该能够弄清楚，让整个家庭的瀑布涟漪（也就是删除父母删除叔叔和孩子）似乎。但是，相反，我得到以下内容：

When I have a Parent and an Uncle with the same Child, and I delete the Parent, it seems like InnoDB should be able to just "figure it out" and let the cascade ripple through the whole family (i.e. deleting the Parent deletes the Uncle and the Child as well). However, instead, I get the following:

  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正在尝试级联 - 删除引用它的叔叔之前的孩子。

InnoDB is trying to cascade-delete the Child before the Uncle(s) that refer to it.

我错过了什么？这个是否因为某种原因我不明白而失败？还是有一些技巧让它发挥作用（或者是MySQL中的一个bug）？

Am I missing something? Is this supposed to fail for some reason I don't understand? Or is there some trick to making it work (or is it a bug in MySQL)?

推荐答案

父删除是触发孩子删除如你所说，我不知道为什么它去叔叔桌前的小孩桌子。我想你必须先看看dbms代码才能确定，但​​是确定有一个算法可以选择哪个表首先级联。

The parent deletion is triggering the child deletion as you stated and I don't know why it goes to the child table before the uncle table. I imagine you would have to look at the dbms code to know for sure, but im sure there is an algorithm that picks which tables to cascade to first.

系统不是真的'弄清楚'东西你的意思在这里的方式，它只是遵循其约束规则。问题是您创建的模式，它遇到一个不会让它进一步传递的约束。

The system does not really 'figure out' stuff the way you imply here and it is just following its constraint rules. The problem is the schema you created in that it encounters a constraint that will not let it pass further.

我看到你在说什么..如果它首先打到叔叔表，它会删除记录，然后删除孩子（而不是从小孩中击中叔叔级联删除）。但即使如此，我也不认为在现实中将会设立一种依赖于这种行为的模式。我认为唯一的方法来确定发生的是查看代码，或者在这里找到一个mysql / postgresql程序员来说明如何处理fk约束。

I see what you are saying.. if it hit the uncle table first it would delete the record and then delete the child (and not hit the uncle cascade from the child deletion). But even so, I don't think a schema would be set up to rely on that kind of behavior in reality. I think the only way to know for sure what is going on is to look through the code or get one of the mysql/postgresql programmers in here to say how it processes fk constraints.

这篇关于处理ON DELETE CASCADE约束的顺序是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！