(mysql.info.gz) ANSI diff Foreign Keys
Info Catalog
(mysql.info.gz) ANSI diff Triggers
(mysql.info.gz) Differences from ANSI
(mysql.info.gz) ANSI diff Views
1.5.5.5 Foreign Keys
....................
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'. InnoDB 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 table type
instead, such as `MyISAM'. (For example, the `MyISAM' storage engine
offers very fast performance for applications that perform only
`INSERT' and `SELECT' operations, because the inserts can be performed
concurrently with retrievals. Table locking.)
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, note that as of MySQL Server 4.0, you can use
multiple-table `DELETE' statements to delete rows from many tables
with a single statement. `DELETE' DELETE.
* A workaround for the lack of `ON DELETE' is to add the appropriate
`DELETE' statement 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 in some instances 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. InnoDB foreign key constraints. As of
MySQL 4.1.1, `mysqldump' generates dump files that take advantage
of this capability automatically when 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;
`JOIN' JOIN. example-Foreign keys.
The `FOREIGN KEY' syntax without `ON DELETE ...' is often used by ODBC
applications to produce automatic `WHERE' clauses.
Info Catalog
(mysql.info.gz) ANSI diff Triggers
(mysql.info.gz) Differences from ANSI
(mysql.info.gz) ANSI diff Views
automatically generated byinfo2html