InnoDB storage engine handles foreign
key constraints is described under the following topics in this
For foreign key usage information and examples, see Section 18.104.22.168, “Using FOREIGN KEY Constraints”.
Foreign key definitions for
are subject to the following conditions:
InnoDBpermits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
InnoDBdoes not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned
InnoDBtable may contain foreign key references or columns referenced by foreign keys.
InnoDBallows a foreign key constraint to reference a non-unique key. This is an
InnoDBextension to standard SQL.
Referential actions for foreign keys of
InnoDB tables are subject to the following
If there are several rows in the parent table that have the same referenced key value,
InnoDBacts in foreign key checks as if the other parent rows with the same key value do not exist. For example, if you have defined a
RESTRICTtype constraint, and there is a child row with several parent rows,
InnoDBdoes not permit the deletion of any of those parent rows.
InnoDBperforms cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.
ON UPDATE CASCADEor
ON UPDATE SET NULLrecurses to update the same table it has previously updated during the cascade, it acts like
RESTRICT. This means that you cannot use self-referential
ON UPDATE CASCADEor
ON UPDATE SET NULLoperations. This is to prevent infinite loops resulting from cascaded updates. A self-referential
ON DELETE SET NULL, on the other hand, is possible, as is a self-referential
ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.
Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows,
FOREIGN KEYconstraints row-by-row. When performing foreign key checks,
InnoDBsets shared row-level locks on child or parent records it has to look at.
InnoDBchecks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until
InnoDBimplements deferred constraint checking, some things are impossible, such as deleting a record that refers to itself using a foreign key.
You can obtain general information about foreign keys and
their usage from querying the
In addition to
SHOW ERRORS, in
the event of a foreign key error involving
InnoDB tables (usually Error 150 in the
MySQL Server), you can obtain a detailed explanation of the
InnoDB foreign key error by
checking the output of