MySQL ON UPDATE CASCADE 不 CASCADEing

Question

假设我有两个下表:

CREATE TABLE post (
  id bigint(20)     NOT NULL    AUTO_INCREMENT,
  text text ,

  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1;

CREATE TABLE post_path (
  ancestorid bigint(20)     NOT NULL    DEFAULT '0',
  descendantid bigint(20)   NOT NULL    DEFAULT '0',
  length int(11)            NOT NULL    DEFAULT '0',

  PRIMARY KEY (ancestorid,descendantid),
  KEY descendantid (descendantid),

  CONSTRAINT f_post_path_ibfk_1 
    FOREIGN KEY (ancestorid) REFERENCES post (id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE,
  CONSTRAINT f_post_path_ibfk_2 
    FOREIGN KEY (descendantid) REFERENCES post (id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
) ENGINE=InnoDB;

并插入这些行:

INSERT INTO 
    post (text)
    VALUES ('a'); #// inserted row by id=1
INSERT INTO 
    post_path (ancestorid ,descendantid ,length) 
    VALUES (1, 1, 0);

当我想更新帖子行 ID 时:

 UPDATE post SET id = '10' WHERE post.id =1

MySQL 说:

#1452 - Cannot add or update a child row: a foreign key constraint fails (test.post_path, CONSTRAINT f_post_path_ibfk_2 FOREIGN KEY (descendantid) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE) 

为什么？怎么了？

编辑:

当我插入这些行时:

INSERT INTO 
    post (text)
    VALUES ('b'); #// inserted row by id=2

INSERT INTO 
    post_path (ancestorid, descendantid, length)
    VALUES (1, 2, 0);

并更新:

UPDATE post SET id = '20' WHERE post.id =2

Mysql 成功更新了子行和父行。那么为什么我不能更新第一篇文章(id=1)？

Answer 1

好的，我通过我也可以访问的测试数据库运行了您的架构和查询，并注意到以下内容；在将两行插入到两个表之后，在进行任何更新之前，数据如下所示:

mysql> select * from post;
+----+------+
| id | text |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from post_path;
+------------+--------------+--------+
| ancestorid | descendantid | length |
+------------+--------------+--------+
|          1 |            1 |      0 |
|          1 |            2 |      0 |
+------------+--------------+--------+
2 rows in set (0.00 sec)

在我发出更新语句后，将 post.id 更新为 20:

mysql> UPDATE `post` SET `id` = '20' WHERE `post`.`id` =2;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from post_path;
+------------+--------------+--------+
| ancestorid | descendantid | length |
+------------+--------------+--------+
|          1 |            1 |      0 |
|          1 |           20 |      0 |
+------------+--------------+--------+
2 rows in set (0.00 sec)

注意 ancestorid 仍然是 1，这似乎是 MySQL 的问题:

If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly. See Section 14.3.5.4, “InnoDB and FOREIGN KEY Constraints”.

您的第一个查询失败的原因是因为 ancestorid 没有更新为 10，但 descendantid 是，并且因为您试图将 post.id 设置为 10，而 post_path 表中的 ancestorid 仍然引用值 1，它将不再存在。

您应该考虑更改您的模式以避免这种情况，并避免更新 auto_increment 列以避免冲突。