MySQL ON UPDATE CASCADE не CASCADEing

Предположим, что у меня есть две таблицы ниже:

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);

Когда я хочу обновить идентификатор строки после следующей строки:

 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)

Почему? что не так?

Edit:

Когда я вставил эти строки:

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)?

Ответ 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)

После того как я выдаю инструкцию update, обновите 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)

Обратите внимание, что дочери все еще 1, это выглядит как проблема с MySQL:

Если вы используете оператор UPDATE с несколькими таблицами с таблицами InnoDB, для которых существуют ограничения внешнего ключа, оптимизатор MySQL может обрабатывать таблицы в порядке, отличном от порядка их отношений между родителями и дочерними элементами. В этом случае утверждение не выполняется и откатывается. Вместо этого обновите одну таблицу и полагайтесь на возможности ON UPDATE, которые InnoDB обеспечивает для изменения соответствующих таблиц. См. Раздел 14.3.5.4, "Ограничения InnoDB и FOREIGN KEY".

Причина, по которой ваш первый запрос терпит неудачу, заключается в том, что ancestorid не обновляется до 10, но descendantid есть, и поскольку вы пытаетесь установить post.id в 10, а ancestorid в таблице post_path все еще ссылается на значение 1, который больше не будет существовать.

Вам следует рассмотреть возможность изменения схемы, чтобы избежать этого, а также не обновлять столбец auto_increment, чтобы избежать коллизий.

Ответ 2

Я считаю, что решение вашей проблемы - удалить descendantid в качестве ограничения и использовать триггер для выполнения обновления в поле.

delimiter $$
CREATE TRIGGER post_trigger
AFTER UPDATE ON post
FOR EACH ROW
BEGIN
UPDATE post_path SET post_path.descendantid = NEW.id WHERE post_path.descendantid = OLD.id
END$$

Ответ 3

Основная причина, по которой вторая работала, заключается в том, что вы сохранили разные значения для ancestorid и descendantid. Когда вы делаете два разных ограничения на основе изменения определенных атрибутов. только первое ограничение будет работать, а не второе. Что происходит в вашей первой попытке обновления.

Ответ 4

Причина, по которой первое обновление терпит неудачу, а второе - нет, потому что во втором случае ваши предки и потомки ссылаются на разные строки в вашей таблице сообщений,

ancestorid   = 1
descendantid = 2

Первое обновление не удается, когда оно пытается обновить post_path.ancestorid, так как при этом ограничение между post.id и post_path.descendantid терпит неудачу, поскольку эти значения больше не будут соответствовать (1! == 10).

Предполагая, что любое заданное сообщение не может быть как предком, так и потомком, проблема здесь заключается только в выполнении первой вставки:

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