Foreign Key constraint fails when trying to delete multiple rows from multiple tables

Question

I want to delete the corresponding rows in staff, login and jobs depending on the Staff.StaffID, given that StaffID and LoginID are foreign keys within jobs. Furthermore, StaffID is a foreign key within login too. When I use the following query, I get an error about foregin key constraint failing. Any ideas?

SQL:

DELETE jobs, login, staff 
FROM jobs 
    INNER JOIN login 
WHERE login.LoginID=jobs.loginID 
AND staff.StaffID = '18';

Obviously this is incomplete. I wanted to continue INNER JOINING before the AND, in order to inner join login.StaffID = staff.StaffID as well as staff.StaffID = jobs.JobID. However, when I do this, I get an error:

DELETE jobs, login, staff 
FROM jobs 
    INNER JOIN login, staff 
WHERE login.LoginID=jobs.LoginID 
AND staff.StaffID=login.StaffID 
AND staff.StaffID = login.LoginID 
AND staff.StaffID = '18';

Which gives the error: "#1451 - Cannot delete or update a parent row: a foreign key constraint fails (younglife.login, CONSTRAINT FK_59 FOREIGN KEY (StaffID) REFERENCES staff (StaffID))"

I can tell that this is inefficent, and I was wondering what is the best way to approach this. Thanks.

Answer 1

You can run three deletes:

DELETE j
    FROM jobs j JOIN
         login l
         ON l.LoginID = j.LoginID 
    WHERE l.StaffID = 18;

DELETE l
   FROM login l
   WHERE l.StaffID = 18;

DELETE s
    FROM staff s
    WHERE s.StaffID = 18;

Be sure that you do the deletions in the correct order.