MySQL外键约束，级联删除 [英] MySQL foreign key constraints, cascade delete
问题描述
如何创建一个SQL语句DELETE ON CASCADE？
如果我删除了一个类别，那么我如何确保它不会删除也与其他类别相关的产品。
数据透视表categories_products创建了另外两个表之间的多对多关系。
类别
- id（INT）
- name（VARCHAR 255）
产品
- id
- 名称
- 价格
categories_products
- categories_id
- products_id
如果您的级联删除了一个产品，因为它是被杀的类别的成员，那么您已经设置了不正确的外键。给定您的示例表，您应该有以下表设置：
CREATE TABLE类别（
id int unsigned not null主键
name VARCHAR（255）缺省值
）Engine = InnoDB;
CREATE TABLE产品（
id int unsigned not null主键，
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） b ON DELETE CASCADE
ON UPDATE CASCADE
）Engine = InnoDB;
通过这种方式，您可以删除产品或类别，只有类别产品中的关联记录会死并肩作战。级联将不会继续向上行，并删除父级产品/类别表。
eg
<$ p产品：靴子，手套，帽子，外套
类别：红色，绿色，蓝色，白色，黑色
产品/猫：红色靴子，绿色手套，红色外套，黑色帽子
如果删除红色类别，则只有红色进入分类表模具，以及两个条目prod / cats：红色靴子和红色外套。
删除将不会级联更远不会拿出靴子和大衣类别。
评论后续：
仍然误解如何级联删除工作。它们只影响on delete cascade定义的表。在这种情况下，级联设置在categories_products表中。如果您删除红色类别，那么在categories_products中级联删除的唯一记录是那些 category_id = red 的记录。它不会触及'category_id = blue'的任何记录，并且不会向前移动到products表，因为在该表中没有定义外键。
<下面是一个更具体的例子：
类别：产品：
+ ---- + ---- - + + ---- + --------- +
| id |名字| | id |名字|
+ ---- + ------ + + ---- + --------- +
| 1 |红色| | 1 |手套|
| 2 |蓝色| | 2 |靴子|
+ --- ++ ------ ++ + ---- + --------- +
products_categories：
+ - ---------- + ------------- +
| product_id | category_id |
+ ------------ + ------------- +
| 1 | 1 | //红色手套
| 1 | 2 | //蓝色手套
| 2 | 1 | //红色靴子
| 2 | 2 | // blue boots
+ ------------ + ------------- +
假设你删除了类别2（蓝色）：
从类别中删除（id = 2）;
DBMS将查看所有具有指向类别表的外键的表，并删除匹配的id为2的记录。由于我们只在 products_categories 中定义了外键关系，所以一旦删除完成，您就会得到这个表：
+ ------------ + ------------- +
| product_id | category_id |
+ ------------ + ------------- +
| 1 | 1 | //红色手套
| 2 | 1 | //红色靴子
+ ------------ + ------------- +
在产品表中没有定义外键，所以级联在那里不起作用，所以你已经还有靴子和手套上市。没有蓝色的靴子，也没有蓝色的手套了。
I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB).
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.
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;
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.
e.g.
products: boots, mittens, hats, coats
categories: red, green, blue, white, black
prod/cats: red boots, green mittens, red coats, black hats
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.
comment followup:
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
+------------+-------------+
Let's say you delete category #2 (blue):
DELETE FROM categories WHERE (id = 2);
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
+------------+-------------+
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.
这篇关于MySQL外键约束，级联删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
