mysql级联删除外键约束_MySQL外键约束，级联删除

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

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

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

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

categories

- id (INT)

- name (VARCHAR 255)

products

- id

- name

- price

categories_products

- categories_id

- products_id

嗨 - 你可能想要修改问题标题，它是关于级联删除真的，而不是专门的数据透视表。

如果你的级联删除了一个产品，因为它是一个被杀死的类别的成员，那么你就是不正确地设置了你的外键。给定示例表，您应该具有以下表格设置：

这样，您可以删除产品或类别，只有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："red boots"和"red coat"。

删除不会进一步级联，也不会删除"靴子"和"大衣"类别。

评论后续：

你仍然误解了级联删除的工作方式。它们仅影响定义"on delete cascade"的表。在这种情况下，级联在"categories_products"表中设置。如果删除"红色"类别，则category_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

+------------+-------------+

products表中没有定义外键，因此级联在那里不起作用，所以你仍然会列出靴子和连指手套。没有'蓝色靴子'，也没有"蓝色手套"了。

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

这是一个非常伟大，高度明显，精彩的插图答案。 感谢您抽出宝贵时间全力以赴。

创建表时，需要指定InnoDB或其他能够进行CASCADE操作的MySQL引擎。 否则将使用MySQL默认值MyISAM，MyISAM不支持CASCADE操作。 为此，只需在最后一个;之前添加ENGINE InnoDB。

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

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

您还需要将以下外键约束添加到链接表：

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子句也可以出现在CREATE TABLE语句中。

创建这些模式对象后，您可以通过发出CALL DeleteCategory(category_ID)(其中category_ID是要删除的类别)来删除类别并获得所需的行为，它将按您的需要运行。但是不要发出正常的DELETE FROM查询，除非您需要更多标准行为(即仅从链接表中删除，并保留products表)。

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

好吧，在那种情况下，我认为Marc B的答案符合你的要求。

您好@Hammerite，请告诉我接受的答案中第三个CREATE TABLE查询中KEY pkey (product_id),的含义是什么？