2

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.

Improve this question
0

1 Answer 1

Reset to default
3

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

Improve this answer
2
  • 2
    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 = ? ; Commented Jan 7, 2016 at 19:03
  • Given the error message this is likely MySQL so yes, that should be possible. Commented Jan 7, 2016 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.