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

Question

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

Answer 1

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