MySQL外键约束、级联删除

我想使用外键来保持完整性并避免孤儿（我已经使用innoDB）。

如何创建 DELETE ON CASCADE 的 SQL 语句？

如果我删除一个类别，那么如何确保它不会删除也与其他类别相关的产品。

数据透视表“categories_products”在其他两个表之间创建多对多关系。

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

products
- id
- name
- price

categories_products
- categories_id
- products_id

如果你的级联删除了一个产品，因为它是被杀死的类别的成员，那么你的外键设置不正确。鉴于您的示例表，您应该具有以下表设置：

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;

这样，您可以删除产品或类别，并且只有categories_products中的关联记录会同时消失。级联不会在树上进一步移动并删除父产品/类别表。

e.g.

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

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

如果删除“红色”类别，则只有类别表中的“红色”条目以及产品/猫这两个条目会消失：“红色靴子”和“红色外套”。

删除不会进一步级联，也不会删除“靴子”和“外套”类别。

评论跟进：

您仍然误解了级联删除的工作原理。它们仅影响定义了“删除级联”的表。在本例中，级联在“categories_products”表中设置。如果删除“红色”类别，则categories_products中唯一会级联删除的记录是那些category_id = red。它不会触及“category_id = blue”的任何记录，也不会继续移动到“products”表，因为该表中没有定义外键。

这是一个更具体的例子：

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);

DBMS 将查看所有具有指向“categories”表的外键的表，并删除匹配 id 为 2 的记录。因为我们只在products_categories，删除完成后您将得到此表：

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

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