如果您的级联删除了nuke一个产品,因为它是被杀类别的成员,那么您的外键设置不正确.根据示例表格,您应该有以下表格设置:
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
如果删除"red"类别,则只有categories表中的"red"条目消失,以及prod/cats中的两个条目:"red boots"和"red coats".
删除将不会进一步级联,也不会删除"靴子"和"外套"类别.
comments 跟进:
你仍然误解了级联删除是如何工作的.它们只影响定义了"on delete cascade"的表.在这种情况下,级联设置在"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
+------------+-------------+
在products
表中没有定义外键,因此级联在那里不起作用,所以仍然列出了靴子和手套.再也没有"蓝色靴子"和"蓝色手套"了.