时间戳

首页 > 解决方案 > ON DELETE CASCADE 删除多余的行

mysql - ON DELETE CASCADE 删除多余的行

问题描述

我有以下表格：

CREATE TABLE workspace (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    uid VARCHAR(255),
    name NVARCHAR(255)
);

CREATE TABLE userGroup (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    workspaceId INT,
    FOREIGN KEY (workspaceId) REFERENCES workspace(id) ON DELETE CASCADE,
    name NVARCHAR(255)
);

CREATE TABLE userWorkspaceMapping (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    workspaceId INT,
    FOREIGN KEY (workspaceId) REFERENCES workspace(id) ON DELETE CASCADE,
    userId INT,
    FOREIGN KEY (userId) REFERENCES user(id),
    userGroupId INT,
    FOREIGN KEY (userGroupId) REFERENCES userGroup(id) ON DELETE CASCADE
);

而这个数据userWorkspaceMapping：

+----+-------------+--------+-------------+
| id | workspaceId | userId | userGroupId |
+----+-------------+--------+-------------+
| 1  |      1      |    1   |      1      |    <------- I want to delete only this row
| 2  |      2      |    1   |      3      |
| 3  |      3      |    1   |      5      |
+-----------------------------------------+

当我删除 aworkspace时，所有三行userWorkspaceMapping都被删除，而不仅仅是第一行。

delete from workspace
where id = 1;

为什么所有内容都被删除userWorkspaceMapping？

这是小提琴：https ://www.db-fiddle.com/f/tHMZy2rnyvoHH13E7dS99A/1

标签： mysqlforeign-keyscascade

解决方案

您的userGroup表还workspace使用 ON DELETE CASCADE 约束进行引用。因此，当您在工作区中删除一行时，所有引用它的行都会userGroup被删除。

这意味着组 1 到 5 被删除。

mysql> delete from workspace where id = 1;
Query OK, 1 row affected (0.03 sec)

mysql> 
mysql> select * from userGroup;
+----+-------------+------+
| id | workspaceId | name |
+----+-------------+------+
|  6 |           2 | b1   |
|  7 |           2 | b2   |
|  8 |           2 | b3   |
|  9 |           2 | b4   |
| 10 |           2 | b5   |
| 11 |           3 | c1   |
| 12 |           3 | c2   |
| 13 |           3 | c3   |
| 14 |           3 | c4   |
| 15 |           3 | c5   |
+----+-------------+------+

因此，该参考组 1 到 5 中的所有行都userWorkspaceMapping将被删除。这占了所有行。

级联可以并且确实级联外键引用中的多个“步骤”。

推荐阅读