Note that foreign keys in SQL are not used to join tables, but are used mostly for checking referential
integrity (foreign key constraints). If you want to get results from multiple tables from a SELECT
statement, you do this by joining tables:
SELECT * from table1,table2 where table1.id = table2.id;
The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors'
CREATE TABLE commands; it doesn't do anything. The FOREIGN KEY syntax without ON
DELETE ... is mostly used for documentation purposes. Some ODBC applications may use this to
produce automatic WHERE clauses, but this is usually easy to override. FOREIGN KEY is sometimes
used as a constraint check, but this check is unnecessary in practice if rows are inserted into the tables
in the right order. MySQL only supports these clauses because some applications require them to exist
(regardless of whether or not they work).
In MySQL, you can work around the problem of ON DELETE ... not being implemented by adding the
appropriate DELETE statement to an application when you delete records from a table that has a
foreign key. In practice this is as quick (in some cases quicker) and much more portable than using
foreign keys.
In the near future we will extend the FOREIGN KEY implementation so that at least the information
will be saved in the table specification file and may be retrieved by mysqldump and ODBC. At a later
stage we will implement the foreign key constraints for application that can't easily be coded to avoid
them.