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
No comments:
Post a Comment