guidesport.blogg.se

To DELETE a row from a parent, the app/user must first DELETE the referencing rows from all children.

Possibly the single most important feature of foreign keys, a DELETE on a parent will fail if child rows exist that reference the parent's row. This again may cause large transactions, excessive locking, and replication lag. Like CASCADE, a single row deletion on the parent may lead to multiple rows updated on child tables.

One advantage is that it's easy to identify the orphan rows: those, and only those rows, will have NULL for parent-referencing columns. It effectively leads to orphaned rows, not very differently from having no foreign key constraints at all. parent_id column in child_table) to NULL for matching rows. DELETE FROM parent_table WHERE id=3) will set the referencing column on children (e.g. With this setup, a DELETE on a parent (e.g. 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. Limited to single MySQL server scope, FOREIGN KEY constraints are impossible to maintain once your data grows and is split over multiple database servers.The way FOREIGN KEY constraints are implemented in MySQL (or, rather, in the InnoDB storage engine) interferes with Online DDL operations.There are two major technical reasons why foreign key constraints are not supported:

We'll soon cover an example of what a schema looks like with and without foreign key constraints so that this small difference is clear.

FOREIGN KEY definition, that is not allowed in your schemas. It is just the enforcement at the database level, the CONSTRAINT. You are encouraged to use the relational model and associate tables by "pointing" rows from one table to another. PlanetScale doesn't support FOREIGN KEY constraints. A constraint also prevents the existence of "orphaned rows" in different methods, as you'll see described soon. Namely, it ensures that a child table can only reference a parent table when the appropriate row exists in the parent table. A table can also refer to itself, as a special case.Ī FOREIGN KEY constraint is a database construct, an implementation that forces the foreign key relationship's integrity (referential integrity). A foreign key typically suggests how you should JOIN tables in most queries. A row in a "parent" table may be referenced by one or more rows in a "child" table. A foreign key is a logical association of rows between two tables, in a parent-child relationship.