MySQL foreign key on same table

Question

I have this table in which I store comments. Every comment has its own ID and a isReply column in case the comment is a reply to another comment. I was wondering if I could set a relation so that when a comment is deleted all comments that are replies to that comment are deleted automatically. I tried setting a foreign key to the isReply column referencing comments.id but I got this error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (_db.#sql-1030_31f, CONSTRAINT #sql-1030_31f_ibfk_1 FOREIGN KEY (isReply) REFERENCES comments (id) ON DELETE CASCADE ON UPDATE NO ACTION)

Answer 1

Your comments table probably still has answers with isReply values referencing comments that have been deleted during your testing. Listen to what MySQL is trying to say:

a foreign key constraint fails

All you have to do is empty the table, define the Foreign Key (your error should disappear), and then you'll have your required behavior. Once the FK is created, no need for triggers, this is why cascades exist.

Note that you will probably want to set the default value for isReply to null using:

ALTER TABLE comments CHANGE isReply isReply integer DEFAULT NULL;

Answer 2

Actually, a foreign key inside the same table is perfectly valid. A 1452 simply means you've one or more items referencing an item which doesn't exists (anymore) and thus is by the definition of you foreign key invalid.

For more info see: Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails it explains why it's failing, how you can find the failing records and how to circumvent you issue.