How can I CASCADE ON DELETE with a Recursive Relationships with MySQL-CodePudding

I've this code:

CREATE TABLE Employee (idEmployee int PRIMARY KEY, nane varchar(100));
CREATE TABLE Friend (idFriendA integer, idFriendB integer, 
                     FOREIGN KEY (idFriendA) REFERENCES Employee (idEmployee) ON DELETE CASCADE, 
                     FOREIGN KEY (idFriendB) REFERENCES Employee (idEmployee) ON DELETE CASCADE);
INSERT INTO Employee (idEmployee, nane) VALUES (0, 'Bob'),(1, 'Jean');
INSERT INTO Friend (idFriendA, idFriendB) VALUES (0, 1);

And I want that if I delete the id 0 in Employee, it will cascade delete the id 1 in Employee by using the relation Friend.

I've tried adding a Primary Key for idFriendB, but it didn't work

CodePudding user response：

The other solution is a trigger but if we try to alter a table in a trigger for delete on the same table we get all error that the table is mutating.
The alternative is to delete the entry in Friends and use a trigger to delete the 2 rows in Employees.
NB I'm not sure what the logic is. What happens if one of the employees had another friend? Do we cascade more deletes? See the case of Bill who loses his friend Jean by the trigger when she loses her friend Bob. Where do we stop? Can an Employee exist without a friend?

CREATE TABLE Employee (
idEmployee int PRIMARY KEY, 
nane varchar(100));
CREATE TABLE Friend (
idFriendA integer, 
idFriendB integer, 
FOREIGN KEY (idFriendA) 
REFERENCES Employee (idEmployee) 
ON DELETE CASCADE, 
FOREIGN KEY (idFriendB) 
REFERENCES Employee (idEmployee) 
ON DELETE CASCADE);
INSERT INTO Employee 
(idEmployee, nane) VALUES 
(0, 'Bob'),
(1, 'Jean'),
(2,'Bill');
INSERT INTO Friend 
(idFriendA, idFriendB) VALUES
 (0, 1),
 (1, 2);

✓

✓

✓

✓

create trigger deleteFriend
before delete on Friend
for each row
delete from Employee
where idEmployee = old.idFriendA 
or idEmployee = old.idFriendB;

✓

select * from Friend;
select * from Employee;

idFriendA | idFriendB
--------: | --------:
        0 |         1
        1 |         2

idEmployee | nane
---------: | :---
         0 | Bob 
         1 | Jean
         2 | Bill

delete from Friend where idFriendA = 0

✓

select * from Friend;
select * from Employee;

idFriendA | idFriendB
--------: | --------:

idEmployee | nane
---------: | :---
         2 | Bill

db<>fiddle here

CodePudding user response：

Following the explanation I would like to suggest that it would be better to use only one table. We use a recursive foreign key to ensure that the parent exists. (We make an exception for the root of our filesystem Id 1 which is /)

CREATE TABLE Files (
fileId integer PRIMARY KEY, 
name varchar(100) NOT NULL,
parentId integer, 
FOREIGN KEY (parentId) 
REFERENCES Files (fileId) 
ON DELETE CASCADE, 
CONSTRAINT Parent_Not_Self CHECK 
(parentId <>  fileId OR fileId = 1));

✓

INSERT INTO Files VALUES
(1,'/',1);

✓

INSERT INTO Files VALUES
(2,'/ twin',2);

Check constraint 'Parent_Not_Self' is violated.

INSERT INTO Files VALUES
(3,'alone',4);

Cannot add or update a child row: a foreign key constraint fails (`db_1032216260`.`Files`, CONSTRAINT `Files_ibfk_1` FOREIGN KEY (`parentId`) REFERENCES `Files` (`fileId`) ON DELETE CASCADE)

INSERT INTO Files VALUES
(2,'/etc',1),
(3,'/home',1),
(4,'/mnt',1),
(5,'/home/user_name',3);

✓

SELECT * FROM Files p
JOIN Files c
ON c.parentId = p.fileId;

fileId | name  | parentId | fileId | name            | parentId
-----: | :---- | -------: | -----: | :-------------- | -------:
     1 | /     |        1 |      1 | /               |        1
     1 | /     |        1 |      2 | /etc            |        1
     1 | /     |        1 |      3 | /home           |        1
     1 | /     |        1 |      4 | /mnt            |        1
     3 | /home |        1 |      5 | /home/user_name |        3

db<>fiddle here