CODEWITHSUNDEEP
mysqlforeign-keyscascade
Sir Rubberduck
Sir Rubberduck

Reputation: 2262

ON DELETE CASCADE deletes extra rows

I have the following tables:

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
);

And this data for userWorkspaceMapping:

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

When I delete a workspace, all three rows in userWorkspaceMapping are deleted, instead of just the first one.

delete from workspace
where id = 1;

Why is everything being deleted in userWorkspaceMapping?

Here's the fiddle: https://www.db-fiddle.com/f/tHMZy2rnyvoHH13E7dS99A/1

Upvotes: 2

Views: 336

Answers (2)

Sir Rubberduck
Sir Rubberduck

Reputation: 2262

There's actually no problem with the CASCADE... I am inserting the wrong test IDs here:

INSERT INTO userWorkspaceMapping (
  userId,
  workspaceId,
  userGroupId
)
VALUES 
  (1, 1, 1),
  (1, 2, 3),  -- <---- should be between 6 and 9
  (1, 3, 5)   -- <---- should be between 10 and 15
 ;

They ALL belong to the deleted workspace... The IDs should be proper ones.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562270

Your userGroup table also references workspace with an ON DELETE CASCADE constraint. So when you delete a row in workspace, all the rows that reference it in userGroup are deleted.

This means groups 1 through 5 are deleted.

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

Hence all the rows in userWorkspaceMapping that reference groups 1 through 5 will be deleted. That accounts for all the rows.

Cascading can and does cascade more than one "step" in the foreign key references.

Upvotes: 2

Related Questions