The MySQL implementation of foreign key constraints differs from the SQL standard in the following key respects:
If there are several rows in the parent table with the same referenced key value,
InnoDB
performs a foreign key check as if the other parent rows with the same key value do not exist. For example, if you define aRESTRICT
type constraint, and there is a child row with several parent rows,InnoDB
does not permit the deletion of any of the parent rows.If
ON UPDATE CASCADE
orON UPDATE SET NULL
recurses to update the same table it has previously updated during the same cascade, it acts likeRESTRICT
. This means that you cannot use self-referentialON UPDATE CASCADE
orON UPDATE SET NULL
operations. 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,
InnoDB
sets 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.No storage engine, including
InnoDB
, recognizes or enforces theMATCH
clause used in referential-integrity constraint definitions. Use of an explicitMATCH
clause does not have the specified effect, and it causesON DELETE
andON UPDATE
clauses to be ignored. Specifying theMATCH
should be avoided.The
MATCH
clause in the SQL standard controls howNULL
values 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 be
UNIQUE
or be declaredNOT NULL
.A
FOREIGN KEY
constraint that references a non-UNIQUE
key is not standard SQL but rather anInnoDB
extension. TheNDB
storage engine, on the other hand, requires an explicit 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 contain
NULL
values is not well defined for operations such asUPDATE
orDELETE CASCADE
. You are advised to use foreign keys that reference onlyUNIQUE
(includingPRIMARY
) andNOT NULL
keys.For storage engines that do not support foreign keys (such as
MyISAM
), MySQL Server parses and ignores foreign key specifications.MySQL parses but ignores “inline
REFERENCES
specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL acceptsREFERENCES
clauses only when specified as part of a separateFOREIGN KEY
specification.Defining a column to use a
REFERENCES
clause has no actual effect and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table. It is important to realize when using this syntax that:tbl_name
(col_name
)MySQL does not perform any sort of check to make sure that
col_name
actually exists intbl_name
(or even thattbl_name
itself exists).MySQL does not perform any sort of action on
tbl_name
such as deleting rows in response to actions taken on rows in the table which you are defining; in other words, this syntax induces noON DELETE
orON UPDATE
behavior whatsoever. (Although you can write anON DELETE
orON UPDATE
clause as part of theREFERENCES
clause, it is also ignored.)This syntax creates a column; it does not create any sort of index or key.
You can use a column so created as a join column, as shown here:
CREATE TABLE person ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(60) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE shirt ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, style ENUM('t-shirt', 'polo', 'dress') NOT NULL, color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL, owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id), PRIMARY KEY (id) ); INSERT INTO person VALUES (NULL, 'Antonio Paz'); SELECT @last := LAST_INSERT_ID(); INSERT INTO shirt VALUES (NULL, 'polo', 'blue', @last), (NULL, 'dress', 'white', @last), (NULL, 't-shirt', 'blue', @last); INSERT INTO person VALUES (NULL, 'Lilliana Angelovska'); SELECT @last := LAST_INSERT_ID(); INSERT INTO shirt VALUES (NULL, 'dress', 'orange', @last), (NULL, 'polo', 'red', @last), (NULL, 'dress', 'blue', @last), (NULL, 't-shirt', 'white', @last); SELECT * FROM person; +----+---------------------+ | id | name | +----+---------------------+ | 1 | Antonio Paz | | 2 | Lilliana Angelovska | +----+---------------------+ SELECT * FROM shirt; +----+---------+--------+-------+ | id | style | color | owner | +----+---------+--------+-------+ | 1 | polo | blue | 1 | | 2 | dress | white | 1 | | 3 | t-shirt | blue | 1 | | 4 | dress | orange | 2 | | 5 | polo | red | 2 | | 6 | dress | blue | 2 | | 7 | t-shirt | white | 2 | +----+---------+--------+-------+ SELECT s.* FROM person p INNER JOIN shirt s ON s.owner = p.id WHERE p.name LIKE 'Lilliana%' AND s.color <> 'white'; +----+-------+--------+-------+ | id | style | color | owner | +----+-------+--------+-------+ | 4 | dress | orange | 2 | | 5 | polo | red | 2 | | 6 | dress | blue | 2 | +----+-------+--------+-------+
When used in this fashion, the
REFERENCES
clause is not displayed in the output ofSHOW CREATE TABLE
orDESCRIBE
:SHOW CREATE TABLE shirt\G *************************** 1. row *************************** Table: shirt Create Table: CREATE TABLE `shirt` ( `id` smallint(5) unsigned NOT NULL auto_increment, `style` enum('t-shirt','polo','dress') NOT NULL, `color` enum('red','blue','orange','white','black') NOT NULL, `owner` smallint(5) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
For information about foreign key constraints, see Section 15.1.20.5, “FOREIGN KEY Constraints”.