Thursday 19 May 2016

Want to delete hierarchical data in MySQL

In MySQL hierarchical data is stored in the form of parent and child tables and often foreign key(s) are used to perform referential action e.g. to help keep data consistent, so it won't allow delete/update operation on a parent row being referenced in a child table. One way to address this issue is to use DELETE/UPDATE CASCADE option - Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. However, such operation is typically not considered when implementing foreign keys, it also has performance implications, referential checks are performed row by row and if a parent record has  millions of related records in child table(s) then this operation would take considerably long to complete, furthermore, quoting from MySQL docs "cascading operation may not be nested more than 15 levels deep," , for more information visit  http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html

Another way to delete hierarchical data is to first identify all child tables (at all N levels deep), this could be difficult if you are dealing with a big schema (with 100s of tables) and then start delete operation in reverse order i.e. delete data from child table first.

Note: you can obtain information about foreign keys by querying the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table.
http://dev.mysql.com/doc/refman/5.7/en/key-column-usage-table.html

To  simplify such operation I have developed a program that can help delete hierarchical data:
https://github.com/aftabakhan/dbtools