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:
InnoDB permits 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.
InnoDB does not currently support
foreign keys for tables with user-defined partitioning. This
means that no user-partitioned
may contain foreign key references or columns referenced by
InnoDB allows a foreign key constraint to
reference a non-unique key. This is an
InnoDB extension to standard
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,
InnoDB acts 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
RESTRICT type constraint, and there is a
child row with several parent rows,
does not permit the deletion of any of those parent rows.
InnoDB performs cascading operations
through a depth-first algorithm, based on records in the
indexes corresponding to the foreign key constraints.
ON UPDATE CASCADE or
SET NULL recurses 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 SET NULL
operations. 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
ON DELETE CASCADE.
Cascading operations may not be nested more than 15 levels
Like MySQL in general, in an SQL statement that inserts,
deletes, or updates many rows,
KEY constraints row-by-row. When performing foreign
InnoDB sets shared row-level
locks on child or parent records it has to look at.
InnoDB checks 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
InnoDB implements 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 126.96.36.199, “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