Удаление из нескольких таблиц с внешними ограничениями

Я пытаюсь удалить из нескольких таблиц. Здесь мои таблицы выглядят как

    A_has_B ---- B ---- C_has_B
(many to many)        (many to many)

Я пытаюсь удалить все строки из A_has_B, B и C_has_B, учитывая ID записи в B. Я использую MySQL с движком хранения innodb с внешними ключами, определенными для A_has_B и C_has_B, ссылающимися на идентификаторы в B.

Я пытаюсь выполнить мое удаление так:

DELETE A_has_B.*, C_has_B.*, B.*

FROM
A

join
B
on (B.B_id = A.B_id)

join
C
on (C.B_id = B.B_id)

where B.B_id IN(1,2, 4);

Проблема заключается в том, что при выполнении запроса mysql жалуется:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`db`.`C`, CONSTRAINT `fk_C` FOREIGN KEY (`B_id`) REFERENCES `B` (`B_id`) ON DELETE NO ACTION ON UPDATE NO)

Как я могу это исправить?

Ответ 1

Самый простой способ - удалить из каждой таблицы отдельно:

-- Remove all connections from A which reference
-- the B-rows you want to remove
DELETE FROM A_has_B
WHERE B_id IN (1,2,4);

-- Remove all connections from C which reference
-- the B-rows you want to remove
DELETE FROM C_has_B
WHERE B_id IN (1,2,4);

-- Finally remove the B-rows
DELETE FROM B
WHERE B_id IN (1,2,4);

MySQL также позволяет удалять из нескольких таблиц в одном выражении. Но нет способа контролировать порядок удаления. Из руководства :

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

Ответ 2

Собственно, в MySQL вы можете отключить проверки ограничений внешнего ключа

SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
--your SQL statements
SET [email protected]_FOREIGN_KEY_CHECKS;

Оператор в первой строке заставляет сервер MySQL отключать проверки внешнего ключа, а последняя строка возвращает их назад на (очень важно). Необходимо помнить две вещи:

Достаточно опасно отключать проверки ограничений, и это не то, что должно быть сделано, например, в производственной БД... самым безопасным способом является использование отдельных операторов.
Всегда включайте проверки ограничений

Ответ 3

Вы можете указать "удалить каскад" по внешнему ключу. Когда вы удаляете родительскую строку, механизм mysql удаляет записи в связанных дочерних таблицах

Ответ 4

Делает удаления в отдельных статутах, и он будет работать.