This section describes differences in the InnoDB storage engine's handling of foreign keys as compared with that of the MySQL Server.
Foreign key definitions for
InnoDB tables 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
tables are subject to the following conditions:
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 will be 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
table, and more information more specific to
InnoDB tables can be found in the
INNODB_SYS_FOREIGN_COLS tables, also
INFORMATION_SCHEMA database. See also
Section 18.104.22.168, “Using FOREIGN KEY Constraints”.
In addition to
SHOW ERRORS, in the
event of a foreign key error involving
tables (usually Error 150 in the MySQL Server), you can obtain a
detailed explanation of the most recent
foreign key error by checking the output of
SHOW ENGINE INNODB