Clever Geek Handbook

DELETE CASCADE crashes when self-defining a MySQL table that is more than 15 levels deep

I have a MySQL table with self-referenced foreign keys. ON DELETE CASCADE works absolutely fine, but I noticed strange behavior that it only works up to 14 levels for the parent object. As soon as I add a 15th level child and try to remove the parent, it starts throwing an error

"Unable to delete or update parent row: foreign key constraint fails

Here is an image for the hierarchy.

enter image description here

Attempting to remove the parent will throw an error.

  • When deleting Child15, the parent can be deleted
  • Without deleting Child15, if I try to delete Child1 it is deleted successfully.

An example circuit to reproduce the behavior is here . Before building the schema, insert this query line with id = 1 instead of id = 2

DELETE FROM table1 WHERE id = 1;

I know of possible workarounds to remove it like

  • SET FOREIGN_KEY_CHECKS = 0
  • Move from bottom to top when deleting

But I want to know what is the known limitation with MySQL for ON CASCADE DELETE?

I am using MySQL server version 5.6

+3


source to share


1 answer


This is the documented behavior:

If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table that it previously updated during the cascade, acts as RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL. This is done to prevent endless loops that result from cascading updates. Self-referencing ON DELETING SET NULL, on the other hand, is possible, as is the self-referencing ON DELETE STAGE. Cascading operations can be up to 15 levels.



Source: InnoDB Constraints and FOREIGN KEY Reference Actions

+5


source






More articles:


All Articles