![]() ![]() The use of ON DELETE CASCADE is controversial. Whether an unsuspecting developer simply assumes a DELETE FROM parent_table WHERE id=3 will at most delete one row, down to surprising behavior such as in REPLACE INTO queries, which actually run an implicit DELETE, leading to mass destruction of data. What seemed like a simple transaction now turns into a massive operation, that involves excessive locking, increased MVCC overhead, impact on replication lag, and more.īut perhaps the greatest danger is the potential unexpected loss of data. You intend to DELETE a single row, but end up deleting hundreds, thousands, or more, rows in multiple tables. ON DELETE CASCADE is a risky and resource-consuming action. This operation runs recursively for all of a parent's children, as well as for their children, should they also employ ON DELETE CASCADE. If you DELETE a row from a parent table, any referencing rows in a child table are subsequently deleted within the same transaction. This is the most greedy, or ambitious action type. The discussion is relevant to ON UPDATE constraints, as well.įoreign keys further support three types of action (illustrated below for ON DELETE): ON DELETE CASCADE In this document, we discuss ON DELETE as it is the more impactful of the two. It lives within the child's table space.įOREIGN KEY constraints are available for row deletion ( ON DELETE) and row updates ( ON UPDATE). A FOREIGN KEY constraint isn't a separate entity. In MySQL, foreign keys are only implemented by the storage engine layer, namely the default and popular InnoDB engine. In other words, if you enforce referential integrity at the application level instead of at the database level, you open the door to all of those benefits. We believe the advantages of Online DDL such as branching, developer-owned schema changes and deployments, non-blocking schema changes, etc., and the advantages of sharding as means of unlimited scaling, outweigh the FOREIGN KEY constraints benefits. This typically happens when you introduce functional partitioning/sharding and/or horizontal sharding.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |