我想使用外键来保持完整性并避免孤立(我已经在使用innoDB).

如何创建一个SQL语句,在级联中删除?

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

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

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

products
- id
- name
- price

categories_products
- categories_id
- products_id

推荐答案

如果您的级联删除了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".

删除将不会进一步级联,也不会删除"靴子"和"外套"类别.

评论跟进:

你仍然误解了级联删除是如何工作的.它们只影响定义了"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表中没有定义外键,因此级联在那里不起作用,所以仍然列出了靴子和手套.再也没有"蓝色靴子"和"蓝色手套"了.

Mysql相关问答推荐

在 MySQL 中转换 JSON 数组值

MYSQL LOAD DATA INFILE 语句适用于工作台,但不适用于 python

使用 ON DUPLICATE KEY 将列增加一定数量 MySQL NodeJS

mysql - 如何加入表中的动态列

如何查看 rdsadmin 在 mysql 实例上的权限?

我将如何构建一个 SQL 查询来从交易表中选择第一次存款、第二次存款和额外存款

添加一个日期字段大于另一个日期字段的要求

MySQL按连续值和计数分组

将每组的总和除以总数

LEFT JOIN 仅第一行

MYSQL OR 与 IN 性能

如何在 MySQL 中进行正则表达式替换?

您的密码不符合当前政策要求

SELECT 列表不在 GROUP BY 子句中并且包含非聚合列 .... 与 sql_mode=only_full_group_by 不兼容

如何在 localhost (xampp) 中打开/关闭 MySQL 严格模式?

Spring Boot JPA 使用 Hibernate 在 TABLE 中插入大写名称

MySQL失败:mysql“错误1524(HY000):插件'auth_socket'未加载”

错误 1067 (42000):“created_at”的默认值无效

SQLSTATE [42000]:语法错误或访问冲突:1055 SELECT 列表的表达式 #3 不在 GROUP BY 子句中并且包含非聚合

用于判断名称是否以元音开头和结尾的 SQL 查询