1.8.2.4 Foreign Keys

The InnoDB storage engine supports checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See Section 14.2.3.4, “InnoDB and FOREIGN KEY Constraints”.

For storage engines other than InnoDB, 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 INFORMATION_SCHEMA.TABLE_CONSTRAINTS table in the INFORMATION_SCHEMA information database.

Foreign key enforcement offers several benefits to database developers:

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 SELECT statement, you do this by performing a join between them:

SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

See Section 13.2.8.2, “JOIN Syntax”, and Section 3.6.6, “Using Foreign Keys”.

The FOREIGN KEY syntax without ON DELETE ... is often used by ODBC applications to produce automatic WHERE clauses.