MySQL's implementation of foreign keys differs from the SQL standard in the following key respects:
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.
FOREIGN KEYconstraint that references a non-
UNIQUEkey is not standard SQL but rather an
ON UPDATE CASCADEor
ON UPDATE SET NULLrecurses to update the same table it has previously updated during the same 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.
In an SQL statement that inserts, deletes, or updates many rows, foreign key constraints (like unique constraints) are checked row-by-row. When performing foreign key checks,
InnoDBsets shared row-level locks on child or parent records that it must examine. MySQL 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. This means that it is not possible to delete a row that refers to itself using a foreign key.
For information about how the
InnoDB storage engine handles
foreign keys, see
Section 14.5.6, “InnoDB and FOREIGN KEY Constraints”.