Внешний ключ MySQL к существующей таблице, удаляет дочерние нарушения

Question

Я хотел бы добавить FK для существующей таблицы, уже заполненной данными с каскадом при удалении. Кто-нибудь знает, возможно ли автоматически удалить все данные, которые нарушают ограничение, чтобы можно было добавить FK без необходимости ручной очистки базы данных. Благодарю.

Answer 1

Это зависит от того, что мы называем "автоматически".
Если речь идет о выполнении SQL-запроса, то это не сложно реализовать.
Все, что нам нужно сделать, это выбрать "неправильные" строки и удалить их.
Как это сделать, лучше всего показать на примере.

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

CREATE TABLE `products` (
 `id` int(11) NOT NULL,
 `name` varchar(16) NOT NULL,
 `type` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `types` (
 `id` int(11) NOT NULL,
 `type` varchar(16) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Мы хотим добавить внешний ключ в таблицу products :

ALTER TABLE `products` 
  ADD CONSTRAINT `FK_some_key` FOREIGN KEY (`type`) REFERENCES `types` (`id`) 
    ON DELETE Cascade ON UPDATE Cascade

Нам нужно удалить все строки из таблицы products где type поля не совпадает с types . id

Сначала мы сделаем запрос SELECT, чтобы убедиться, что мы выбрали правильные строки:

SELECT `products`.*
FROM `products` LEFT JOIN `types`
ON `products`.`type` = `types`.`id`
WHERE `types`.`id` IS NULL

Если выбор выглядит нормально, измените SELECT на DELETE:

DELETE `products`.*
FROM `products` LEFT JOIN `types`
ON `products`.`type` = `types`.`id`
WHERE `types`.`id` IS NULL

Теперь у нас есть таблица, которая готова добавить внешний ключ.