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.

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

  • 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 = ? ; – ypercubeᵀᴹ Jan 7 '16 at 19:03
  • Given the error message this is likely MySQL so yes, that should be possible. – Lennart Jan 7 '16 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.