Ограничения внешнего ключа MySQL, каскадное удаление

Ответ 1

Если ваш каскад удаляет nuke из-за того, что он был членом категории, которая была убита, вы неправильно настроили свои внешние ключи. Учитывая ваши примерные таблицы, вы должны настроить следующую таблицу:

CREATE TABLE categories (
    id int unsigned not null primary key,
    name VARCHAR(255) default null
)Engine=InnoDB;

CREATE TABLE products (
    id int unsigned not null primary key,
    name VARCHAR(255) default null
)Engine=InnoDB;

CREATE TABLE categories_products (
    category_id int unsigned not null,
    product_id int unsigned not null,
    PRIMARY KEY (category_id, product_id),
    KEY pkey (product_id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE
)Engine=InnoDB;

Таким образом, вы можете удалить продукт ИЛИ категорию, и только связанные записи в category_products будут умирать вместе. Каскад не будет перемещаться дальше по дереву и удалять таблицу родительского продукта/категории.

например.

products: boots, mittens, hats, coats
categories: red, green, blue, white, black

prod/cats: red boots, green mittens, red coats, black hats

Если вы удаляете "красную" категорию, тогда умирает только "красная" запись в таблице категорий, а также две записи prod/cats: "красные ботинки" и "красные пальто".

Удаление не будет каскадироваться дальше и не будет выводить категории "boots" и "coat".

комментарий:

вы по-прежнему не понимаете, как работают каскадные удаления. Они влияют только на таблицы, в которых определяется "на каскаде удаления". В этом случае каскад устанавливается в таблице "categories_products". Если вы удалите категорию "red", единственными записями, которые будут каскадировать delete в category_products, являются те, где category_id = red. Он не будет касаться каких-либо записей, где "category_id = синий", и он не будет перемещаться вперед в таблицу "продукты", поскольку в этой таблице нет внешнего ключа.

Вот более конкретный пример:

categories:     products:
+----+------+   +----+---------+
| id | name |   | id | name    |
+----+------+   +----+---------+
| 1  | red  |   | 1  | mittens |
| 2  | blue |   | 2  | boots   |
+---++------+   +----+---------+

products_categories:
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1          | 1           | // red mittens
| 1          | 2           | // blue mittens
| 2          | 1           | // red boots
| 2          | 2           | // blue boots
+------------+-------------+

Скажем, вы удаляете категорию # 2 (синий):

DELETE FROM categories WHERE (id = 2);

СУБД рассмотрит все таблицы, которые имеют внешний ключ, указывающий на таблицу категорий, и удалите записи, в которых совпадающий идентификатор равен 2. Поскольку мы определили только отношение внешнего ключа в products_categories, вы заканчиваете с этой таблицей после завершения удаления:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1          | 1           | // red mittens
| 2          | 1           | // red boots
+------------+-------------+

В таблице products нет внешнего ключа, поэтому каскад не будет работать там, поэтому у вас все еще есть ботинки и рукавицы. Там просто нет "синих сапог" и "синих рукавиц" больше.

Ответ 2

Меня смутил ответ на этот вопрос, поэтому я создал тестовый пример в MySQL, надеюсь, что это поможет

-- Schema
CREATE TABLE T1 (
    `ID` int not null auto_increment,
    `Label` varchar(50),
    primary key (`ID`)
);

CREATE TABLE T2 (
    `ID` int not null auto_increment,
    `Label` varchar(50),
    primary key (`ID`)
);

CREATE TABLE TT (
    `IDT1` int not null,
    `IDT2` int not null,
    primary key (`IDT1`,`IDT2`)
);

ALTER TABLE `TT`
    ADD CONSTRAINT `fk_tt_t1` FOREIGN KEY (`IDT1`) REFERENCES `T1`(`ID`) ON DELETE CASCADE,
    ADD CONSTRAINT `fk_tt_t2` FOREIGN KEY (`IDT2`) REFERENCES `T2`(`ID`) ON DELETE CASCADE;

-- Data
INSERT INTO `T1` (`Label`) VALUES ('T1V1'),('T1V2'),('T1V3'),('T1V4');
INSERT INTO `T2` (`Label`) VALUES ('T2V1'),('T2V2'),('T2V3'),('T2V4');
INSERT INTO `TT` (`IDT1`,`IDT2`) VALUES
(1,1),(1,2),(1,3),(1,4),
(2,1),(2,2),(2,3),(2,4),
(3,1),(3,2),(3,3),(3,4),
(4,1),(4,2),(4,3),(4,4);

-- Delete
DELETE FROM `T2` WHERE `ID`=4; -- Delete one field, all the associated fields on tt, will be deleted, no change in T1
TRUNCATE `T2`; -- Can't truncate a table with a referenced field
DELETE FROM `T2`; -- This will do the job, delete all fields from T2, and all associations from TT, no change in T1

Ответ 3

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

CREATE PROCEDURE `DeleteCategory` (IN category_ID INT)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN

DELETE FROM
    `products`
WHERE
    `id` IN (
        SELECT `products_id`
        FROM `categories_products`
        WHERE `categories_id` = category_ID
    )
;

DELETE FROM `categories`
WHERE `id` = category_ID;

END

Вам также необходимо добавить следующие ограничения внешнего ключа в таблицу ссылок:

ALTER TABLE `categories_products` ADD
    CONSTRAINT `Constr_categoriesproducts_categories_fk`
    FOREIGN KEY `categories_fk` (`categories_id`) REFERENCES `categories` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `Constr_categoriesproducts_products_fk`
    FOREIGN KEY `products_fk` (`products_id`) REFERENCES `products` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE

Предложение CONSTRAINT может, конечно, также отображаться в инструкции CREATE TABLE.

Создав эти объекты схемы, вы можете удалить категорию и получить нужное поведение, выпустив CALL DeleteCategory(category_ID) (где category_ID - это категория, которую нужно удалить), и она будет вести себя так, как вы хотите. Но не отправляйте обычный запрос DELETE FROM, если вы не хотите более стандартного поведения (т.е. Удалите только из таблицы привязки и оставьте таблицу products в отдельности).