user580950 user580950 - 4 months ago 13
MySQL Question

Mysql database error - deleting records by a join query

I am trying to delete a single row from tbl_user and multiple rows from user_subscription_log, my join query is:

DELETE tbl_user, user_subscription_log
FROM tbl_user
INNER JOIN user_subscription_log ON tbl_user.id = user_subscription_log.user_id
WHERE tbl_user.id='256'


The error returned is:

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (
databasename
.
user_subscription_log
, CONSTRAINT
foreirgn_key_user_id
FOREIGN KEY (
user_id
) REFERENCES
tbl_user
(
id
))

Answer

It's failing because a child record exists in user_subscription_log table and, no 'on delete' clause is set in Foreign key.

I'd execute two delete queries:

DELETE FROM user_subscription_log WHERE user_id = ?;

DELETE FROM tbl_user where id = ?;

Another option is to set 'on delete cascade' clause in the foreign key definition, as explained here.