MySQL MySQL外键约束，级联删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2914936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL foreign key constraints, cascade delete
提问by Cudos
I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB).
我想使用外键来保持完整性并避免孤儿（我已经使用 innoDB）。
How do I make a SQL statment that DELETE ON CASCADE?
如何制作在级联上删除的 SQL 语句？
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_products”在另外两个表之间创建多对多关系。
categories
- id (INT)
- name (VARCHAR 255)
products
- id
- name
- price
categories_products
- categories_id
- products_id
回答by Marc B
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.
这样，您可以删除一个产品或一个类别，并且只有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
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'.
如果删除“红色”类别，则只有类别表中的“红色”条目以及两个条目 prod/cats：“红色靴子”和“红色外套”消失。
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.
您仍然误解了级联删除的工作原理。它们只影响定义了“删除级联”的表。在这种情况下，级联设置在“categories_products”表中。如果您删除“红色”类别，则categories_products 中将级联删除的唯一记录是category_id = red. 它不会触及任何 'category_id = blue' 的记录，也不会前进到“products”表，因为该表中没有定义外键。
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):
假设您删除了类别 #2（蓝色）：
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:
DBMS 将查看外键指向 'categories' 表的所有表，并删除匹配 id 为 2 的记录。由于我们只在 中定义了外键关系products_categories，因此一旦删除完成：
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1 | 1 | // red mittens
| 2 | 1 | // red boots
+------------+-------------+
There's no foreign key defined in the productstable, 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.
products表中没有定义外键，所以级联在那里不起作用，所以你仍然列出了靴子和手套。不再有“蓝色靴子”和“蓝色手套”了。
回答by Abderrahim
I got confused by the answer to this question, so I created a test case in MySQL, hope this helps
我对这个问题的答案感到困惑，所以我在 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
回答by Hammerite
I think (I'm not certain) that foreign key constraints won't do precisely what you want given your table design. Perhaps the best thing to do is to define a stored procedure that will delete a category the way you want, and then call that procedure whenever you want to delete a category.
我认为（我不确定）鉴于您的表设计，外键约束不会完全满足您的要求。也许最好的做法是定义一个存储过程，该过程将按照您想要的方式删除类别，然后在您想要删除类别时调用该过程。
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
You also need to add the following foreign key constraints to the linking table:
您还需要将以下外键约束添加到链接表中：
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
The CONSTRAINT clause can, of course, also appear in the CREATE TABLE statement.
当然，CONSTRAINT 子句也可以出现在 CREATE TABLE 语句中。
Having created these schema objects, you can delete a category and get the behaviour you want by issuing CALL DeleteCategory(category_ID)(where category_ID is the category to be deleted), and it will behave how you want. But don't issue a normal DELETE FROMquery, unless you want more standard behaviour (i.e. delete from the linking table only, and leave the productstable alone).
创建了这些架构对象后，您可以删除一个类别并通过发出CALL DeleteCategory(category_ID)（其中 category_ID 是要删除的类别）来获得您想要的行为，并且它将按照您想要的方式运行。但是不要发出普通DELETE FROM查询，除非您想要更标准的行为（即仅从链接表中删除，并且不理会该products表）。
