Mysql database error - deleting records by a join query

Question

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))

Community · Accepted Answer · 2017-05-23 11:44:17Z

2

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.

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Jul 24, 2016 at 22:05

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Strawberry Over a year ago

I'd go with 'another option'

Collectives™ on Stack Overflow

Mysql database error - deleting records by a join query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related