Alter ON DELETE CASCADE in 1 table

Question

I'm really new to ON DELETE CASCADE. How can I delete child comments when the parent comment gets deleted in a table?

Table Schema

'CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) NOT NULL,
  `comment` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=latin1'

Current Query (fails)

ALTER TABLE comments
   ADD CONSTRAINT `delete_child`
   FOREIGN KEY (`parent`)
   REFERENCES `comments` (`id`)
   ON DELETE CASCADE

Error Code: 1452.

Cannot add or update a child row: a foreign key constraint fails
(`App`.`#sql-1405_16b7`, CONSTRAINT `delete_child` FOREIGN KEY (`parent`)
REFERENCES `comments` (`id`) ON DELETE CASCADE)

This SQLFiddle shows my situation. It works in SQLFiddle but not in MySQLWorkbench.

Answer 1

The error indicates that you have invalid references in the parentId column.

This error shows when you are trying to insert a row with an invalid reference, update an existing row with an invalid reference, or delete a row that is referenced. You also get it when you are trying to create a foreign key constraint on a column that is already populated and contains references to non-existing rows, which is what happens in your case.

Solution: check the contents of the parentId column and correct/remove the invalid references.