MySQL外键约束，级联删除

categories
- id (INT)
- name (VARCHAR 255)

products
- id
- name
- price

categories_products
- categories_id
- products_id

#1 楼

如果您的级联因为某个产品被杀而删除了nuke产品，那么您的外键设置不正确。给定示例表，您应该设置以下表：一起死级联将不会沿着树走得更远，并删除父产品/类别表。

例如

红色”类别，则只有类别表中的“红色”条目消失，而两个条目prod / cats消失：“红色靴子”和“红色外套”。

删除操作不会层叠

评论跟进：

您仍然误解了级联删除的工作方式。它们仅影响定义了“删除时级联”的表。在这种情况下，级联设置在“ categories_products”表中。如果删除“红色”类别，则将在category_products中级联删除的唯一记录是category_id = red。它不会触及'category_id = blue'的任何记录，也不会继续移动到“ products”表，因为该表中没有定义外键。 ：

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;

假设您删除类别2（蓝色）：

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

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

DBMS将看起来在所有具有指向“类别”表的外键的表上，并删除匹配ID为2的记录。由于我们仅在products_categories中定义了外键关系，因此删除完成后，您将得到该表：

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
+------------+-------------+

products表中没有定义外键，因此级联在该表中不起作用，因此仍然列出了靴子和连指手套。再也没有“蓝色靴子”和“蓝色手套”了。

#2 楼

我对这个问题的答案感到困惑，因此我在MySQL中创建了一个测试用例，希望这对您有所帮助。

#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表保留）。