GNU Info

Info Node: (mysql.info)Missing Foreign Keys

(mysql.info)Missing Foreign Keys


Next: Broken Foreign KEY Prev: Missing Triggers Up: Missing functions
Enter node , (file) or (file)node

Foreign Keys
............

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;

Note: `JOIN'. Note: example-Foreign keys.

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.

Broken Foreign KEY
Reasons NOT to use foreign keys constraints

automatically generated by info2www version 1.2.2.9