MySQL外键约束，级联删除

2021-11-20 00:00:00 foreign-keys mysql innodb

我想使用外键来保持完整性并避免孤儿(我已经使用了 innoDB).

I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB).

我如何制作一个 DELETE ON CASCADE 的 SQL 语句?

How do I make a SQL statment that DELETE ON CASCADE?

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

If I delete a category then how do I make sure that it would not delete products that also are related to other categories.

数据透视表categories_products"在另外两个表之间创建多对多关系.

The pivot table "categories_products" creates a many-to-many relationship between the two other tables.

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

products
- id
- name
- price

categories_products
- categories_id
- products_id

推荐答案

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

If your cascading deletes nuke a product because it was a member of a category that was killed, then you've set up your foreign keys improperly. Given your example tables, you should have the following table setup:

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 中的关联记录会一起消失.级联不会在树上进一步移动并删除父产品/类别表.

This way, you can delete a product OR a category, and only the associated records in categories_products will die alongside. The cascade won't travel farther up the tree and delete the parent product/category table.

例如

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

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

如果删除 'red' 类别，则只有类别表中的 'red' 条目以及 prod/cats 的两个条目:'red boots' 和 'redcoats' 消失.

If you delete the 'red' category, then only the 'red' entry in the categories table dies, as well as the two entries prod/cats: 'red boots' and 'red coats'.

删除不会进一步级联，也不会删除靴子"和外套"类别.

The delete will not cascade any farther and will not take out the 'boots' and 'coats' categories.

评论跟进:

您仍然误解了级联删除的工作原理.它们只影响定义了删除级联"的表.在这种情况下，级联设置在categories_products"表中.如果删除红色"类别，则categories_products 中唯一会级联删除的记录是那些category_id = red 的记录.它不会触及任何 'category_id = blue' 的记录，也不会前进到products"表，因为该表中没有定义外键.

you're still misunderstanding how cascaded deletes work. They only affect the tables in which the "on delete cascade" is defined. In this case, the cascade is set in the "categories_products" table. If you delete the 'red' category, the only records that will cascade delete in categories_products are those where category_id = red. It won't touch any records where 'category_id = blue', and it would not travel onwards to the "products" table, because there's no foreign key defined in that table.

这是一个更具体的例子:

Here's a more concrete example:

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(蓝色):

Let's say you delete category #2 (blue):

DELETE FROM categories WHERE (id = 2);

DBMS 会查看所有有外键指向 'categories' 表的表，并删除匹配 id 为 2 的记录.因为我们只在 products_categories，一旦删除完成，你就会得到这个表:

the DBMS will look at all the tables which have a foreign key pointing at the 'categories' table, and delete the records where the matching id is 2. Since we only defined the foreign key relationship in products_categories, you end up with this table once the delete completes:

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

products 表中没有定义外键，所以级联在那里不起作用，所以你仍然列出了靴子和手套.只是不再有蓝色靴子"和蓝色手套"了.

There's no foreign key defined in the products table, so the cascade will not work there, so you've still got boots and mittens listed. There's just no 'blue boots' and no 'blue mittens' anymore.

相关文章