mysql - ON DELETE CASCADE 删除额外的行

Question

我有以下表格:

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      |
+-----------------------------------------+

当我删除一个 workspace , userWorkspaceMapping 中的所有三行被删除， 而不仅仅是第一个 .

delete from workspace
where id = 1;

为什么userWorkspaceMapping中的所有内容都被删除了?
这是 fiddle : https://www.db-fiddle.com/f/tHMZy2rnyvoHH13E7dS99A/1

Answer 1

您的 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   |
+----+-------------+------+

因此， userWorkspaceMapping 中的所有行引用组 1 到 5 将被删除。这占所有行。
级联可以并且确实在外键引用中级联不止一个“步骤”。