MySQL foreign key on one table

Question

I have this table in which comments are stored. Each comment has its own identifier and isReply column if the comment is a response to another comment. I was wondering if I can establish a relationship so that when you delete a comment, all comments that are answers to that comment are deleted automatically. I tried to set the foreign key in the isReply column referring to comments.id , but I got this error:

# 1452 - Unable to add or update a child row: foreign key failed ( _db . #sql-1030_31f , CONSTRAINT #sql-1030_31f_ibfk_1 FOREIGN KEY ( isReply ) LINKS comments ( id ) ENABLE CASCADE ON UPDATE NO ACTION)

Answer 1

Your comment table probably still has answers with isReply values ​​referring to comments that were removed during testing. Listen to what MySQL is trying to say:

 a foreign key constraint fails 

All you have to do is clear the table, define the foreign key (your error should disappear), and then you will have the required behavior. After creating an FK, there is no need for triggers, so there are cascades.

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

 ALTER TABLE comments CHANGE isReply isReply integer DEFAULT NULL; 

Answer 2

In fact, the foreign key inside the same table works great. Parameter 1452 simply means that you have one or more elements that reference an element that does not exist (anymore) and, therefore, by definition of your foreign key is invalid.

For more information, see Mysql Error 1452 - Unable to add or update child row: foreign key constraint fails , this explains why it fails, how you can find unsuccessful records and workarounds for your problem.

Answer 3

Create a trigger to delete all entries with the same criteria. When an entry is deleted in the comment table.

Check trigger documentation

And look at this question

Otherwise, you cannot use a column that is in the same table as the foreign key.