InnoDB storage engine supports checking
of foreign key constraints, including
ON DELETE, and
ON UPDATE. See
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