CODEWITHSUNDEEP
mysql
Saheb Guchhait
Saheb Guchhait

Reputation: 33

How to delete records with foreign key constraint in mysql single query

DELETE logins, roles 
FROM logins
INNER JOIN roles ON logins.`LOGIN_ID`=roles.`LOGIN_ID`
WHERE roles.`LOGIN_ID`=25774;

Query: DELETE logins, roles FROM logins INNER JOIN roles ON logins.LOGIN_ID=roles.LOGIN_ID WHERE roles.LOGIN_ID=25774

Error Code: 1451 Cannot delete or update a parent row: a foreign key constraint fails (foodapp.roles, CONSTRAINT roles_ibfk_1 FOREIGN KEY (LOGIN_ID) REFERENCES logins (LOGIN_ID))

Execution Time : 0 sec Transfer Time : 0 sec Total Time : 0.038 sec

Upvotes: 1

Views: 2078

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

If you don't have ON CASCADE DELETE constraint setup, you will need to Delete the row from child table first. And, then fire a separate query to delete it from the parent table as well.

DELETE FROM roles 
WHERE roles.`LOGIN_ID`=25774;

And, then delete from parent table:

DELETE FROM logins
WHERE logins.`LOGIN_ID`=25774;

From Documentation:

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

If ON CASCADE DELETE has been used in the Foreign Key definition, then all you need to do is delete from the Parent table only. It will automatically delete the corresponding rows from the Child table(s).

Upvotes: 1

Related Questions