InnoDB storage engine supports checking
of foreign key constraints, including
ON DELETE, and
ON UPDATE. See
Section 188.8.131.52, “
FOREIGN KEY Constraints”.
For storage engines other than
NDB, MySQL Server parses the
FOREIGN KEY syntax in
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. You can obtain more detailed information about
foreign keys from the
table. In addition,
InnoDB provides a
containing information about foreign keys on
InnoDB tables; see
Section 20.30, “
INFORMATION_SCHEMA Tables for
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
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices