79493

the table has no holes in the middle and the inserts can be performed concurrently wlth retrievals. See Section 8.10.3, “Concurrent Inserts”.)

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.2, “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 morę 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 enabling you to temporarily

disable foreign key checks when reloading a table that depends on other tables. See Section 5.4.5,

“innoDB and foreign key Constraints”. As of MySQL 4.1.1, mysąldump generates dump files

that take advantage of this capability automatically when they are reloaded.)

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