MySQL外键约束，级联删除

Question

我希望使用外键来保持数据完整性并避免孤儿数据（我已经在使用innoDB）。

如何编写一个SQL语句实现级联删除？

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

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

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

products
- id
- name
- price

categories_products
- categories_id
- products_id

- Cudos

---

嗨 - 你可能想修改问题标题，它实际上是关于级联删除，而不仅仅是透视表。 - Paddyslacker

Answer 1

如果你的级联删除操作导致一个产品被删除，因为它是某个被删除的类别的成员，那么你的外键设置是有问题的。根据你提供的表格示例，你应该按照以下方式设置表格：

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中关联的记录才会被一起删除。级联操作不会向树形结构更高层级移动并删除父级产品/类别表。

例如：

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

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

如果你删除了“红色”类别，那么只有“红色”在类别表中的条目会被删除，以及两个条目prod/cats：“红色靴子”和“红色外套”。

删除操作不会进一步级联，也不会删除“鞋子”和“外套”这两个类别。

评论回复：

你仍然误解了级联删除的工作原理。它们只影响定义了“on delete cascade”的表。在本例中，级联删除是在“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
+------------+-------------+

在产品表中没有定义外键，因此级联删除不会生效，所以你仍然可以看到列出的靴子和手套。只是没有'蓝色靴子'和'蓝色手套'了。

- Marc B

---

我想我用错误的方式写了我的问题。如果我删除一个类别，那么如何确保它不会删除与其他类别相关的产品？ - Cudos

这是一篇非常优秀、表述清晰且图文并茂的回答。感谢您花费时间将其全部写出。 - scottb

创建表时，需要指定InnoDB或其他支持“CASCADE”操作的MySQL引擎。否则，将使用MySQL默认的MyISAM，而MyISAM不支持“CASCADE”操作。要实现这一点，只需在最后一个“;”之前添加“ENGINE InnoDB”。 - Patrick

Answer 2

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

- Abderrahim

Answer 3

我认为（不确定）根据你的表设计，外键约束不会完全满足你的要求。也许最好的方法是定义一个存储过程，按照你想要的方式删除某个类别，然后在想要删除类别时调用该存储过程。

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

约束条件（CONSTRAINT clause）可以在CREATE TABLE语句中出现。

创建完这些模式对象后，您可以通过发出CALL DeleteCategory(category_ID)（其中category_ID是要删除的类别）来删除类别并获得所需的行为，它将按照您的意愿进行操作。但是，除非您想要更标准的行为（即仅从链接表中删除，并保留products表），否则不要发出普通的DELETE FROM查询。

- Hammerite

---

我觉得我把我的问题写错了。如果我删除一个类别，那么我如何确保它不会删除与其他类别相关的产品。 - Cudos

好的，那么我认为Marc B的回答符合你的需求。 - Hammerite

你好@Hammerite，能否告诉我在被接受的答案中第三个CREATE TABLE查询中 KEY pkey (product_id), 的含义是什么？ - Siraj Alam

Answer 4

外键约束和级联删除是管理数据库的重要组成部分，尤其适用于使用MySQL的系统。我们将讨论一些有助于保持数据有序和准确的想法。这些概念对于良好地管理数据很重要。

外键规则确保数据库中的表之间的关联。一个表中的外键与另一个表中的主键相关联。外键约束是确保数据库中表之间数据完整性的规则。在数据库中，外键列连接两个表，它将引用表连接到被引用表以创建关系，这有助于组织信息。

应用外键约束意味着外键列中的值必须与被引用表中的主键列匹配。这确保了数据的一致性，防止了孤立记录的创建或数据不一致。

让我们看看两个表："Orders"和"Customers"。 "Orders"表有一个名为"customer_id"的列，"customer_id"指的是"Customers"表中的"id"。"Orders"表中的"customer_id"列必须与"Customers"表中的"id"相匹配。

级联删除是与外键约束相关的操作。当您在一个表中删除一条记录时，另一个表中的相关记录也将被删除。它不需要手动删除即可发生。这确保了数据的一致性，并防止了数据库中的孤立记录。级联更新是外键约束的另一个选项。

假设您在“Customers”表中删除了一个客户记录。如果您在“Orders”表的外键约束上设置了级联删除，那么所有与该客户关联的订单都将被删除。这简化了数据管理并保持了数据库的完整性。

级联删除也可以应用于多层级的关系。我们可以通过配置级联删除来设置当删除订单时如何删除相关项目。这适用于具有引用“Orders”表的外键的“Items”表。这个效果会沿着关系往下传递，在每个层级上删除相关记录。

使用级联删除时要小心，因为如果操作不当可能会导致意外数据丢失。在打开级联删除之前三思而后行。由于需要考虑数据依赖性，它可能会影响整个系统。

外键约束除了级联删除，还可以使用级联更新。如果启用级联更新，主键的更改也会改变另一个表中相关的外键值。这保持了数据完整性，并确保表之间的关系保持有效。

外键约束和级联删除在MySQL数据库中非常重要。它们可以保持数据的组织性，使其更易于管理。这有助于保持数据的准确性。建立引用完整性可以提高数据库的可靠性和一致性。

在创建数据库结构时，思考周到是很重要的。这包括考虑表之间的关系。我们还要考虑到级联删除可能出现的问题。适当的规划和测试对于在系统中正确使用这些功能至关重要。

- Shiva Sharma

---

这是ChatGPT的输出，该输出在Stack Overflow上被禁止。我强烈建议您在陷入更大麻烦之前删除它：我们在这里非常严肃地对待剽窃问题。 - tchrist

你的回答可以通过提供更多支持性信息来改进。请编辑以添加进一步的细节，例如引用或文档，以便他人能够确认你的回答是否正确。你可以在帮助中心找到关于如何撰写好回答的更多信息。 - Community

这并没有回答问题。一旦你拥有足够的声望，你就可以评论任何帖子；相反，提供不需要提问者澄清的答案。- 来自审查 - Yogendra