I need help with a SQL-query that results in an error

Question

I get the following error when I try to delete a record in the user table that has an entry in the feedback table:

e.g. when a record in the feedback table has a reference to the user table

Table

1451 - Cannot delete or update a parent row: a foreign key constraint fails (kbs.feedback, CONSTRAINT fk_feedback_user1 FOREIGN KEY (user_id) REFERENCES user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION)

The relationship between the two tables:

Relationship

I hope someone could help me into the right direction and that my question is clear enough. I've followed a database design course but forgot a lot of stuff.

Answer 1

The user you are trying to delete have rows in the feedback table. Given your foreign key definition:

CONSTRAINT fk_feedback_user1 
FOREIGN KEY (user_id) 
 REFERENCES user (user_id) 
 ON DELETE NO ACTION 
 ON UPDATE NO ACTION

You need to first delete from the feedback table:

delete from feedback where user_id = ?;
delete from user where user_id = ?;

An alternative is to change the foreign key to:

CONSTRAINT fk_feedback_user1 
FOREIGN KEY (user_id) 
 REFERENCES user (user_id) 
 ON DELETE CASCADE 
 ON UPDATE NO ACTION

Any rows in the feedback table for that user will then be deleted automatically when you delete a user

• ypercubeᵀᴹ: One more option: delete from both tables in one statement: DELETE f, u FROM user AS u LEFT JOIN feedback AS f ON u.user_id = f.user_id WHERE u.user_id = ? ;
• Lennart - Slava Ukraini: Given the error message this is likely MySQL so yes, that should be possible.