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
InnoDB table may contain foreign key
references or columns referenced by foreign keys.
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
InnoDB tables are subject to the following
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,
InnoDB 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
UPDATE 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
ON UPDATE CASCADE or
ON UPDATE SET NULL operations. This is to
prevent infinite loops resulting from cascaded updates. A
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,
KEY constraints row-by-row. When performing
foreign key checks,
InnoDB sets shared
row-level locks on child or parent records it has to look
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 processed. Until
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
also in the
INFORMATION_SCHEMA database. See
also Section 184.108.40.206, “Using FOREIGN KEY Constraints”.
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 most
InnoDB foreign key error by checking
the output of
ENGINE INNODB STATUS.