Удаление SQL с ограничением на соединение и внешний ключ

Question

С помощью 10.0.31-MariaDB-1~jessie,

У меня есть две таблицы: provider_contact а также provider_contact_x_role где contact_id колонка в provider_contact_x_role таблица имеет ссылку на внешний ключ id колонка в provider_contact Таблица.

Когда я запускаю следующий запрос, результатом является успех:

DELETE cr, c
FROM provider_contact_x_role AS cr
INNER JOIN provider_contact AS c
ON cr.contact_id = c.id
WHERE c.is_test_contact = 0;

Когда я запускаю следующий запрос:

DELETE cr, c
FROM provider_contact_x_role AS cr
INNER JOIN provider_contact AS c
ON cr.contact_id = c.id
WHERE c.email_address <> '[e-mail]';

Результатом является следующая ошибка:

Cannot delete or update a parent row: a foreign key constraint fails 
(`ins_db`.`provider_contact_x_role`, CONSTRAINT 
`FK_contact_id--provider_contact.id` FOREIGN KEY (`contact_id`) 
REFERENCES `provider_contact` (`id`))

Обратите внимание, что единственная разница между этими двумя запросами заключается в WHERE состояние.

Вопросы:

1) Есть ли что-нибудь в SQL, гарантирующее, что при выполнении этих запросов строки из provider_contact_x_role удаляются в первую очередь?

2) Любая идея, что делает разницу между этими двумя запросами (почему 1-й успешно, а 2-й отказывает)?

Answer 1

Извлечено из документации MySQL:

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

И если вы используете EXPLAIN вы увидите, что он предпочитает сначала фильтровать (и удалять) по основной таблице, так как это необходимо для выполнения JOIN.

Если вы используете INNER JOINты мог бы попробовать STRAIGHT_JOIN что то же самое, что и INNER JOIN за исключением того, что левая таблица всегда читается перед правой таблицей и помещает таблицы в желаемом порядке удаления.

В качестве альтернативы каскаду вы можете отключить внешние ключи в операторе удаления с помощью SET FOREIGN_KEY_CHECKS=0;.

Answer 2

Не могли бы вы сначала попытаться удалить записи в вашей таблице provider_contact, а не provider_contact_x_role, потому что он обеспечивает ссылочную целостность из внешнего ключа contact_id.

DELETE c, cr
FROM provider_contact AS c
INNER JOIN provider_contact_x_role AS cr
ON c.id = cr.contact_id
WHERE c.email_address <> '[e-mail]';