1.8.6.2. `FOREIGN KEY` Constraints

Foreign keys let you cross-reference related data across tables, and foreign key constraints help keep this spread-out data consistent.

MySQL supports ON UPDATE and ON DELETE foreign key references in CREATE TABLE and ALTER TABLE statements. The available referential actions are RESTRICT (the default), CASCADE, SET NULL, and NO ACTION.

SET DEFAULT is also supported by the MySQL Server but is currently rejected as invalid by InnoDB and NDB. Since MySQL does not support deferred constraint checking, NO ACTION is treated as RESTRICT. For the exact syntax supported by MySQL for foreign keys, see Section 13.1.17.2, "Using FOREIGN KEY Constraints".

MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE are allowed, but their use should be avoided, as they cause the MySQL Server to ignore any ON DELETE or ON UPDATE clause used in the same statement. MATCH options do not have any other effect in MySQL, which in effect enforces MATCH SIMPLE semantics full-time.

MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.

You can obtain information about foreign keys from the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table. An example of a query against this table is shown here:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
        CONSTRAINT_NAME      > FROM
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE      > WHERE
        REFERENCED_TABLE_SCHEMA IS NOT NULL;+--------------+---------------+-------------+-----------------+| TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME | CONSTRAINT_NAME |+--------------+---------------+-------------+-----------------+| fk1          | myuser        | myuser_id   | f               || fk1          | product_order | customer_id | f2              || fk1          | product_order | product_id  | f1              |+--------------+---------------+-------------+-----------------+3 rows in set (0.01 sec)

Information about foreign keys on InnoDB tables can also be found in the INNODB_SYS_FOREIGN and INNODB_SYS_FOREIGN_COLS tables, in the INFORMATION_SCHEMA database.

Currently, only InnoDB tables support foreign keys. See Section 5.4.5, "InnoDB and FOREIGN KEY Constraints", for information specific to foreign key support in InnoDB.

Deviations from SQL Standards

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, 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.
A FOREIGN KEY constraint that references a non-UNIQUE key is not standard SQL but rather an InnoDB extension.
If ON UPDATE CASCADE or ON UPDATE SET NULL recurses 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 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.
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.

For information how InnoDB foreign keys differ from the SQL standard, see Section 5.4.5, "InnoDB and FOREIGN KEY Constraints".

1.8.6.2. FOREIGN KEY Constraints

Deviations from SQL Standards

1.8.6.2. `FOREIGN KEY` Constraints