This section describes differences in the InnoDB storage engine' 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
does not currently support foreign keys for tables with user-defined partitioning. This means that no
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
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
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
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 self-referential
ON UPDATE CASCADE or
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 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
FOREIGN KEY constraints row-by-row. When performing foreign key checks,
InnoDB sets shared row-level locks on child or parent records it has to
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
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
INFORMATION_SCHEMA.KEY_COLUMN_USAGE table, and more information more specific to
InnoDB tables can be found in the
INNODB_SYS_FOREIGN_COLS tables, also in the
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
InnoDB foreign key error by checking the output of
SHOW ENGINE INNODB STATUS.