14

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)

Andy G
  • 19,232
  • 5
  • 47
  • 69
php_nub_qq
  • 15,199
  • 21
  • 74
  • 144
  • Could you make a simplified sqlfiddle which shows the above error? BTW, Don't forget this limitation: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html *A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.* – biziclop Aug 25 '13 at 17:00
  • `DELETE c1 FROM comments c1 LEFT JOIN comments c2 ON c2.id=c1.isReply WHERE c2.id IS NULL`. This should delete all replies to deleted comments. Then adding a FK will work. Instead of deleting one can alternatively `UPDATE ... LEFT JOIN ... ON ... SET c1.isReply=NULL WHERE ...`. – Martin Schneider Mar 18 '17 at 09:26

3 Answers3

10

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;
edsioufi
  • 8,297
  • 3
  • 36
  • 42
  • Well you've shed some light into the situation. I emptied the whole table and added the key successfully, however when I now try to add a record I get `Cannot add or update a child row: a foreign key constraint fails (`_db`.`comments`, CONSTRAINT `opinions_ibfk_1` FOREIGN KEY (`isReply`) REFERENCES `comments` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION)` – php_nub_qq Aug 25 '13 at 17:26
  • @php_nub_qq You should add the comments first, and *then* the answers. It won't work the other way around. Try inserting a single comment, then a single answer to it. Debug with selects from the table to make sure the referenced parent exists. – edsioufi Aug 25 '13 at 17:32
  • I am inserting a comment, not a reply to a comment. Is this weird or what? – php_nub_qq Aug 25 '13 at 17:35
  • 1
    @php_nub_qq Ah, of course, you should also allow `null` on the FK column (`isReply`). See [this post](http://stackoverflow.com/questions/212939/how-do-i-modify-a-mysql-column-to-allow-null). – edsioufi Aug 25 '13 at 17:38
  • 1
    Exactly as @edsioufi says and @biziclop pointed out. The `isReply` should be nullable and NULL by default. Also i'd recommend you set `ON UPDATE` to `CASCADE` as well. – vollie Aug 25 '13 at 17:42
  • @vollie agree on `ON UPDATE` cascade. – edsioufi Aug 25 '13 at 17:44
  • Oh I have allowed and defaulted NULL and successfully inserted a bunch of comments but now I can't seem to pick them up.. This part of the select query seems to mess things up `AND `isReply`=NULL` :( – php_nub_qq Aug 25 '13 at 17:48
  • @php_nub_qq So you can insert now, that's the good news. Try removing the quotes around `NULL`. Also, I have edited my answer to include the default to null part :). – edsioufi Aug 25 '13 at 17:50
  • There are no quotes around the null, I am trying this in SQL directly and if I remove the part where it says isReply=NULL it picks up the comments, but unfortunately I need this in order to be able to display replies because I use the same algorithm to get main comments and subcomments :( – php_nub_qq Aug 25 '13 at 17:54
  • @php_nub_qq Change `=Null` to `IS NULL`. :) – edsioufi Aug 25 '13 at 17:55
  • Be careful if you use both ON UPDATE CASCADE and triggers in mysql : rows deleted by the cascade will bypass delete trigger. If you had logic in the trigger, then that logic is not run. See https://stackoverflow.com/questions/6041064/trigger-calls-in-cascade-deleting – David V. May 05 '20 at 09:15
10

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.

Community
  • 1
  • 1
vollie
  • 1,005
  • 1
  • 11
  • 20
-1

Create a trigger to delete all the records with the same criteria. When a record is deleted in comments Table .

Check triggers documentation

And look at this Question

Otherwise , you can't use column that is in the same table as foreign key.

Community
  • 1
  • 1
Charaf JRA
  • 8,249
  • 1
  • 34
  • 44
  • But in case I create a trigger, will it be still present in case of export and import of the whole database :/ – php_nub_qq Aug 25 '13 at 16:58
  • if you associate to delete comment a query for deleting replies,they will be deleted from database,so no export is possible,if your want to store them in databse try adding boolean field 'isDeleted' in your table and change his value from false to true – Charaf JRA Aug 25 '13 at 17:03