1.8.2.4 Foreign Key Differences

The InnoDB storage engine supports checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See Section 14.9.6, “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. You can obtain more detailed information about foreign keys from the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS table.

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.9.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.

Deviations from SQL Standards

MySQL's implementation of foreign keys differs from the SQL standard in the following key respects:

For information how InnoDB foreign keys differ from the SQL standard, see Section 14.9.6, “InnoDB and FOREIGN KEY Constraints”.