On DELETE CASCADE fails in self referencing MySQL table having depth more than 15 levels

Question

I have a MySQL table with self referencing foreign keys. ON DELETE CASCADE works absolutely fine in it but I noticed a weird behavior that it is working only up to 14 levels for a parent entity. As soon as I add an 15th level child and try to delete the parent it starts throwing error

"Cannot delete or update a parent row: a foreign key constraint fails"

Here is the image for the hierarchy.

enter image description here

Trying to delete the Parent will throw error.

On Deleting the Child15, Parent can be deleted
Without deleting Child15, If I try to delete Child1 then it gets deleted successfully.

The following sample (also available as a fiddle) reproduces the error when a row with 15 descendents is deleted:

DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `parent` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_table1_1` (`parent`),
  CONSTRAINT `FK_table1_1` FOREIGN KEY (`parent`) REFERENCES `table1` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


INSERT INTO table1 (id, name, parent)
  VALUES
(1, "Parent", null),
(2, "Child 1", 1),
(3, "Child 2", 2),
(4, "Child 3", 3),
(5, "Child 4", 4),
(6, "Child 5", 5),
(7, "Child 6", 6),
(8, "Child 7", 7),
(9, "Child 8", 8),
(10, "Child 9", 9),
(11, "Child 10", 10),
(12, "Child 11", 11),
(13, "Child 12", 12),
(14, "Child 13", 13),
(15, "Child 14", 14),
(16, "Child 15", 15)
;

-- generates the error
DELETE FROM table1 WHERE id=1;

If instead a row with 14 descendents is deleted, there is no error:

DELETE FROM table1 WHERE id=2;

I know the possible workarounds to delete it like

SET FOREIGN_KEY_CHECKS=0
Moving bottom up while deleting

But I want to know that is this some known limitation with MySQL for ON CASCADE DELETE?

I am using MySQL server version 5.6

score 7 · Accepted Answer · edited Nov 02 '21 at 13:59

This is documented behavior:

If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. 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.

Source: InnoDB and FOREIGN KEY Constraints, Referential Actions

On DELETE CASCADE fails in self referencing MySQL table having depth more than 15 levels

1 Answers1