🔗 MySQL's ON DELETE CASCADE has a "15-level wall"

With ON DELETE CASCADE, deeper hierarchies in MySQL face a daunting challenge: a limit of 15 levels. When attempting to delete a parent node from a self-referencing tree structure, cascading deletions will halt if the descendant chain reaches this depth, resulting in a rollback of the entire operation. The author documented several issues with cascading operations, including the fact that throws of ERROR 1215 occur when adding cascading deletions to STORED generated columns. To address this, switching to VIRTUAL columns allows for successful cascade definitions. While the cascade method is simpler, it's also slower and prone to accidental deletions of entire subtrees. An alternative, which involves manual deletions, allows for more control and avoids the limitations of cascading deletions. The article ends by outlining alternative designs for storing trees that can bypass these complications entirely.