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 though, but this is usually
easy to override. FOREIGN KEY is sometimes used as a constraint check,
but this check is in practice unnecessary if rows are inserted into the tables
in the right order. MySQL only supports these commands because some
application require them to exists (but not work!).
In MySQL one can work around the problem that ON DELETE
... isn't implemented by adding the approative DELETE statement to
the application when one deletes records from a table that has
FOREIGN KEY. In practice this is as quick (in some case quicker)
and much more portable than using FOREIGN KEY.
Foreign keys are something that makes life very complicated, because the foreign key definition must be stored in a database and implementing them would mean that the whole 'nice approach' of using files that can be moved, copied and removed would be destroyed.
In the near future we will extend FOREIGN KEYS so that at least
the information will be saved and may be retrieved by mysqldump and
ODBC.
Go to the first, previous, next, last section, table of contents.