(mysql.info) ansi-diff-foreign-keys

Info Catalog

(mysql.info) ansi-diff-triggers

(mysql.info) differences-from-ansi

(mysql.info) ansi-diff-views

 
 1.9.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'. See  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 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  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, 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  delete.
 
    * 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 
      innodb-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  join, and  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) ansi-diff-triggers

(mysql.info) differences-from-ansi

(mysql.info) ansi-diff-views