In MySQL Server 3.23.44 and up, the InnoDB
storage engine supports checking of foreign key constraints,
including CASCADE, ON
DELETE, and ON UPDATE. See
Section 14.2.6.4, “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. In the future, the implementation will be
extended to store this information in the table specification
file so that it may be retrieved by
mysqldump and ODBC. At a later stage,
foreign key constraints will be implemented for
MyISAM tables as well.
Foreign key enforcement offers several benefits to database
developers:
Assuming proper design of the relationships, foreign key
constraints make it more difficult for a programmer to
introduce an inconsistency into the database.
Centralized checking of constraints by the database server
makes it unnecessary to perform these checks on the
application side. This eliminates the possibility that
different applications may not all check the constraints
in the same way.
Using cascading updates and deletes can simplify the
application code.
Properly designed foreign key rules aid in documenting
relationships between tables.
Do keep in mind that these benefits come at the cost of
additional overhead for the database server to perform the
necessary checks. Additional checking by the server affects
performance, which for some applications may be sufficiently
undesirable as to be avoided if possible. (Some major
commercial applications have coded the foreign key logic at
the application level for this reason.)
MySQL gives database developers the choice of which approach
to use. If you don't need foreign keys and want to avoid the
overhead associated with enforcing referential integrity, you
can choose another storage engine instead, such as
MyISAM. (For example, the
MyISAM storage engine offers very fast
performance for applications that perform only
INSERT and SELECT
operations. In this case, the table has no holes in the middle
and the inserts can be performed concurrently with retrievals.
See Section 7.3.2, “Table Locking Issues”.)
If you choose not to take advantage of referential integrity
checks, keep the following considerations in mind:
In the absence of server-side foreign key relationship
checking, the application itself must handle relationship
issues. For example, it must take care to insert rows into
tables in the proper order, and to avoid creating orphaned
child records. It must also be able to recover from errors
that occur in the middle of multiple-record insert
operations.
If ON DELETE is the only referential
integrity capability an application needs, you can achieve
a similar effect as of MySQL Server 4.0 by using
multiple-table DELETE statements to
delete rows from many tables with a single statement. See
Section 13.2.1, “DELETE Syntax”.
A workaround for the lack of ON DELETE
is to add the appropriate DELETE
statements to your application when you delete records
from a table that has a foreign key. In practice, this is
often as quick as using foreign keys and is more portable.
Be aware that the use of foreign keys can sometimes lead to
problems:
Foreign key support addresses many referential integrity
issues, but it is still necessary to design key
relationships carefully to avoid circular rules or
incorrect combinations of cascading deletes.
It is not uncommon for a DBA to create a topology of
relationships that makes it difficult to restore
individual tables from a backup. (MySQL alleviates this
difficulty by allowing you to temporarily disable foreign
key checks when reloading a table that depends on other
tables. See
Section 14.2.6.4, “FOREIGN KEY Constraints”. As of
MySQL 4.1.1, mysqldump generates dump
files that take advantage of this capability automatically
when they are reloaded.)
Note that 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, t2 WHERE t1.id = t2.id;
See Section 13.2.7.1, “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.
