InnoDB storage engine supports checking
of foreign key constraints, including
ON DELETE, and
ON UPDATE. See
InnoDB and FOREIGN KEY Constraints.
For storage engines other than
MySQL Server parses the
FOREIGN KEY syntax
CREATE TABLE statements, but
does not use or store it. This information is also present in
mysqldump, and can be retrieved using
Connector/ODBC. You can see which tables have foreign key
constraints by checking the
table in the
database. In MySQL 5.1.16 and later, you can obtain more
detailed information about foreign keys from the
Foreign key enforcement offers several benefits to database developers:
Assuming proper design of the relationships, foreign key constraints make it more difficult for a programmer to introduce an inconsistency into the database.
Centralized checking of constraints by the database server makes it unnecessary to perform these checks on the application side. This eliminates the possibility that different applications may not all check the constraints in the same way.
Using cascading updates and deletes can simplify the application code.
Properly designed foreign key rules aid in documenting relationships between tables.
Foreign keys in SQL are used to check and enforce referential
integrity, not to join tables. If you want to get results from
multiple tables from a
statement, you do this by performing a join between them:
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
FOREIGN KEY syntax without
DELETE ... is often used by ODBC applications to
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.
FOREIGN KEY constraint that
references a non-
UNIQUE key is not
standard SQL but rather an
ON UPDATE CASCADE or
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
ON UPDATE CASCADE
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
ON DELETE CASCADE.
Cascading operations may not be nested more than 15 levels
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
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.