If there are several rows in the parent table with the same referenced key value,InnoDBperforms a foreign key check as if the other parent rows with the same key value do not exist. For example, if you define aRESTRICTtype constraint, and there is a child row with several parent rows,InnoDBdoes not permit the deletion of any of the parent rows.

IfON UPDATE CASCADEorON UPDATE SET NULLrecurses to update thesame tableit has previously updated during the same cascade, it acts likeRESTRICT. This means that you cannot use self-referentialON UPDATE CASCADEorON UPDATE SET NULLoperations. This is to prevent infinite loops resulting from cascaded updates. A self-referentialON DELETE SET NULL, on the other hand, is possible, as is a self-referentialON 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 theentire SQL statementhas been processed. This means that it is not possible to delete a row that refers to itself using a foreign key.

No storage engine, includingInnoDB, recognizes or enforces theMATCHclause used in referential-integrity constraint definitions. Use of an explicitMATCHclause does not have the specified effect, and it causesON DELETEandON UPDATEclauses to be ignored. Specifying theMATCHshould be avoided.

TheMATCHclause in the SQL standard controls howNULLvalues in a composite (multiple-column) foreign key are handled when comparing to a primary key in the referenced table. MySQL essentially implements the semantics defined byMATCH SIMPLE, which permits a foreign key to be all or partiallyNULL. In that case, a (child table) row containing such a foreign key can be inserted even though it does not match any row in the referenced (parent) table. (It is possible to implement other semantics using triggers.)

MySQL requires that the referenced columns be indexed for performance reasons. However, MySQL does not enforce a requirement that the referenced columns beUNIQUEor be declaredNOT NULL.

AFOREIGN KEYconstraint that references a non-UNIQUEkey is not standard SQL but rather anInnoDBextension. TheNDB存储引擎,另一方面,需要一个实验licit unique key (or primary key) on any column referenced as a foreign key.

The handling of foreign key references to nonunique keys or keys that containNULLvalues is not well defined for operations such asUPDATEorDELETE CASCADE. You are advised to use foreign keys that reference onlyUNIQUE(includingPRIMARY) andNOT NULLkeys.

MySQL parses but ignores“inlineREFERENCESspecifications”(as defined in the SQL standard) where the references are defined as part of the column specification. MySQL acceptsREFERENCESclauses only when specified as part of a separateFOREIGN KEYspecification. For storage engines that do not support foreign keys (such asMyISAM), MySQL Server parses and ignores foreign key specifications.