MySQL 外键约束，级联删除

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

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

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

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

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


products
- id
- name
- price


categories_products
- categories_id
- products_id
277167 次浏览
小开

我认为(我不确定)外键约束不会完全满足您对表设计的要求。也许最好的办法是定义一个存储过程，该存储过程将按照您希望的方式删除类别，然后在您希望删除类别时调用该过程。

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

当然，CONSTRINT 子句也可以出现在 CREATETABLE 语句中。

创建了这些模式对象之后，您可以删除一个类别，并通过发出 CALL DeleteCategory(category_ID)(其中 type _ ID 是要删除的类别)获得您想要的行为，它将按照您想要的方式运行。但是不要发出一个正常的 DELETE FROM查询，除非您想要更标准的行为(即只从链接表中删除，而不要使用 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;

通过这种方式，您可以删除一个产品或一个类别，并且只有 Category _ products 中的相关记录会随之消失。级联不会沿着树向上移动，也不会删除父产品/类别表。

例如:。

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


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

如果删除“ red”类别，那么只有类别表中的“ red”条目会消失，以及两个条目 prod/cat: “ red boots”和“ red cott”。

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

评论后续行动:

你还是不明白级联删除是如何工作的。它们只影响定义了“ on delete 层叠”的表。在这种情况下，级联是在“ Category _ products”表中设置的。如果您删除了“ red”类别，那么将级联删除 Category _ products 中的唯一记录就是那些 category_id = red的记录。它不会接触任何“ type _ 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);

数据库管理系统将查看所有有外键指向“ Category”表的表，并删除匹配 id 为2的记录。因为我们只在 products_categories中定义了外键关系，所以一旦删除完成，您就会得到这个表:

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

在 products表中没有定义外键，所以级联在这里不起作用，所以您仍然需要列出靴子和手套。现在已经没有“蓝靴子”和“蓝手套”了。

小开

我对这个问题的答案感到困惑，所以我在 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

提交答案