Author: paul
Date: 2008-07-29 18:51:23 +0200 (Tue, 29 Jul 2008)
New Revision: 11424
Log:
r32578@arctic: paul | 2008-07-29 11:50:14 -0500
Updates regarding foreign key semantics, MATCH, RESTRICT. (Ken)
(Bug#34944)
Modified:
trunk/refman-4.1/se-innodb-core.xml
trunk/refman-4.1/sql-syntax-data-definition.xml
trunk/refman-5.0/se-innodb-core.xml
trunk/refman-5.0/sql-syntax-data-definition.xml
trunk/refman-5.1/se-innodb-core.xml
trunk/refman-5.1/sql-syntax-data-definition.xml
trunk/refman-6.0/se-innodb-core.xml
trunk/refman-6.0/sql-syntax-data-definition.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:33040
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:32577
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:33040
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:32578
Modified: trunk/refman-4.1/se-innodb-core.xml
===================================================================
--- trunk/refman-4.1/se-innodb-core.xml 2008-07-29 16:51:12 UTC (rev 11423)
+++ trunk/refman-4.1/se-innodb-core.xml 2008-07-29 16:51:23 UTC (rev 11424)
Changed blocks: 6, Lines Added: 72, Lines Deleted: 23; 6881 bytes
@@ -2351,7 +2351,8 @@
<remark role="help-description-begin"/>
<para>
- The syntax of a foreign key constraint definition in
+ <literal>InnoDB</literal> supports foreign key constraints. The
+ syntax for a foreign key constraint definition in
<literal>InnoDB</literal> looks like this:
</para>
@@ -2417,7 +2418,9 @@
<listitem>
<para>
- In the referenced table, there must be an index where the
+ <literal>InnoDB</literal> allows a foreign key to reference
+ any index column or group of columns. However, in the
+ referenced table, there must be an index where the
referenced columns are listed as the
<emphasis>first</emphasis> columns in the same order.
</para>
@@ -2460,7 +2463,9 @@
KEY</literal> clause. When the user attempts to delete or update
a row from a parent table, and there are one or more matching
rows in the child table, <literal>InnoDB</literal> supports five
- options regarding the action to be taken:
+ options regarding the action to be taken. If <literal>ON
+ DELETE</literal> or <literal>ON UPDATE</literal> are not
+ specified, the default action is <literal>RESTRICT</literal>.
</para>
<itemizedlist>
@@ -2518,14 +2523,15 @@
<listitem>
<para>
<literal>RESTRICT</literal>: Rejects the delete or update
- operation for the parent table. <literal>NO ACTION</literal>
- and <literal>RESTRICT</literal> are the same as omitting the
- <literal>ON DELETE</literal> or <literal>ON UPDATE</literal>
- clause. (Some database systems have deferred checks, and
- <literal>NO ACTION</literal> is a deferred check. In MySQL,
- foreign key constraints are checked immediately, so
- <literal>NO ACTION</literal> and <literal>RESTRICT</literal>
- are the same.)
+ operation for the parent table. Specifying
+ <literal>RESTRICT</literal> (or <literal>NO
+ ACTION</literal>) is the same as omitting the <literal>ON
+ DELETE</literal> or <literal>ON UPDATE</literal> clause.
+ (Some database systems have deferred checks, and <literal>NO
+ ACTION</literal> is a deferred check. In MySQL, foreign key
+ constraints are checked immediately, so <literal>NO
+ ACTION</literal> is the same as
+ <literal>RESTRICT</literal>.)
</para>
</listitem>
@@ -2552,9 +2558,9 @@
<literal>InnoDB</literal> requires indexes on foreign keys and
referenced keys so that foreign key checks can be fast and not
require a table scan. Starting with MySQL 4.1.2, the index on
- the foreign key is created automatically. In older versions, the
- indexes must be created explicitly or the creation of foreign
- key constraints fails.
+ the foreign key is created automatically if no index on the key
+ columns exists. In older versions, the indexes must be created
+ explicitly or the creation of foreign key constraints fails.
</para>
<para>
@@ -2570,19 +2576,62 @@
error in the server.
</para>
- <para>
- Starting from MySQL 3.23.50, <literal>InnoDB</literal> does not
- check foreign key constraints on those foreign key or referenced
- key values that contain a <literal>NULL</literal> column.
- </para>
+ <important>
+ <para>
+ For users familiar with the ANSI/ISO SQL Standard, please note
+ that no storage engine, including <literal>InnoDB</literal>,
+ recognizes or enforces the <literal>MATCH</literal> clause
+ used in referential integrity constraint definitions. Use of
+ an explicit <literal>MATCH</literal> clause will not have the
+ specified effect, and also causes <literal>ON DELETE</literal>
+ and <literal>ON UPDATE</literal> clauses to be ignored. For
+ these reasons, specifying <literal>MATCH</literal> should be
+ avoided.
+ </para>
- <note>
<para>
- Currently, triggers are not activated by cascaded foreign key
- actions.
+ The <literal>MATCH</literal> clause in the SQL standard
+ controls how <literal>NULL</literal> values in a composite
+ (multiple-column) foreign key are handled when comparing to a
+ primary key. Starting from MySQL 3.23.50,
+ <literal>InnoDB</literal> does not check foreign key
+ constraints on those foreign key or referenced key values that
+ contain a <literal>NULL</literal> column.
+ <literal>InnoDB</literal> essentially implements the semantics
+ defined by <literal>MATCH NONE</literal>, which allow a
+ foreign key to be all or partially <literal>NULL</literal>. In
+ that case, the (child table) row containing such a foreign key
+ is allowed to be inserted, and does not match any row in the
+ referenced (parent) table.
</para>
- </note>
+ <para>
+ Additionally, MySQL and <literal>InnoDB</literal> require that
+ the referenced columns be indexed for performance. However,
+ the system does not enforce a requirement that the referenced
+ columns be <literal>UNIQUE</literal> or be declared
+ <literal>NOT NULL</literal>. The handling of foreign key
+ references to non-unique keys or keys that contain
+ <literal>NULL</literal> values are not well defined for
+ operations such as <literal>UPDATE</literal> or
+ <literal>DELETE CASCADE</literal>. You are advised to use
+ foreign keys that reference only <literal>UNIQUE</literal> and
+ <literal>NOT NULL</literal> keys.
+ </para>
+
+ <para>
+ Furthermore, <literal>InnoDB</literal> does not recognize or
+ support <quote>inline <literal>REFERENCES</literal>
+ specifications</quote>, as defined in the SQL standard where
+ the references are defined as part of the column
+ specification. <literal>InnoDB</literal> accepts
+ <literal>REFERENCES</literal> clauses only when specified as
+ part of a separate <literal>FOREIGN KEY</literal>
+ specification. All other engines ignore all foreign key
+ specifications.
+ </para>
+ </important>
+
<para>
You cannot create a table with a column name that matches the
name of an internal InnoDB column (including
Modified: trunk/refman-4.1/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-4.1/sql-syntax-data-definition.xml 2008-07-29 16:51:12 UTC (rev 11423)
+++ trunk/refman-4.1/sql-syntax-data-definition.xml 2008-07-29 16:51:23 UTC (rev 11424)
Changed blocks: 1, Lines Added: 21, Lines Deleted: 11; 2497 bytes
@@ -2291,18 +2291,28 @@
note that no storage engine, including
<literal>InnoDB</literal>, recognizes or enforces the
<literal>MATCH</literal> clause used in referential
- integrity constraint definitions. The
- <literal>MATCH</literal> clause in the SQL standard controls
- how <literal>NULL</literal> values in a composite
+ integrity constraint definitions. Use of an explicit
+ <literal>MATCH</literal> clause will not have the specified
+ effect, and also causes <literal>ON DELETE</literal> and
+ <literal>ON UPDATE</literal> clauses to be ignored. For
+ these reasons, specifying <literal>MATCH</literal> should be
+ avoided.
+ </para>
+
+ <para>
+ The <literal>MATCH</literal> clause in the SQL standard
+ controls how <literal>NULL</literal> values in a composite
(multiple-column) foreign key are handled when comparing to
- a primary key. <literal>InnoDB</literal> essentially
- implements the semantics defined by <literal>MATCH
- NONE</literal>, which allow a foreign key to be all or
- partially <literal>NULL</literal>. In that case, the (child
- table) row containing such a foreign key is allowed to be
- inserted, and does not match any row in the referenced
- (parent) table. It is possible to implement other semantics
- using triggers.
+ a primary key. Starting from MySQL 3.23.50,
+ <literal>InnoDB</literal> does not check foreign key
+ constraints on those foreign key or referenced key values
+ that contain a <literal>NULL</literal> column.
+ <literal>InnoDB</literal> essentially implements the
+ semantics defined by <literal>MATCH NONE</literal>, which
+ allow a foreign key to be all or partially
+ <literal>NULL</literal>. In that case, the (child table) row
+ containing such a foreign key is allowed to be inserted, and
+ does not match any row in the referenced (parent) table.
</para>
<para>
Modified: trunk/refman-5.0/se-innodb-core.xml
===================================================================
--- trunk/refman-5.0/se-innodb-core.xml 2008-07-29 16:51:12 UTC (rev 11423)
+++ trunk/refman-5.0/se-innodb-core.xml 2008-07-29 16:51:23 UTC (rev 11424)
Changed blocks: 6, Lines Added: 71, Lines Deleted: 20; 6637 bytes
@@ -2473,8 +2473,8 @@
<remark role="help-description-begin"/>
<para>
- <literal>InnoDB</literal> also supports foreign key constraints.
- The syntax for a foreign key constraint definition in
+ <literal>InnoDB</literal> supports foreign key constraints. The
+ syntax for a foreign key constraint definition in
<literal>InnoDB</literal> looks like this:
</para>
@@ -2539,7 +2539,9 @@
<listitem>
<para>
- In the referenced table, there must be an index where the
+ <literal>InnoDB</literal> allows a foreign key to reference
+ any index column or group of columns. However, in the
+ referenced table, there must be an index where the
referenced columns are listed as the
<emphasis>first</emphasis> columns in the same order.
</para>
@@ -2582,7 +2584,9 @@
KEY</literal> clause. When the user attempts to delete or update
a row from a parent table, and there are one or more matching
rows in the child table, <literal>InnoDB</literal> supports five
- options regarding the action to be taken:
+ options regarding the action to be taken. If <literal>ON
+ DELETE</literal> or <literal>ON UPDATE</literal> are not
+ specified, the default action is <literal>RESTRICT</literal>.
</para>
<itemizedlist>
@@ -2638,14 +2642,15 @@
<listitem>
<para>
<literal>RESTRICT</literal>: Rejects the delete or update
- operation for the parent table. <literal>NO ACTION</literal>
- and <literal>RESTRICT</literal> are the same as omitting the
- <literal>ON DELETE</literal> or <literal>ON UPDATE</literal>
- clause. (Some database systems have deferred checks, and
- <literal>NO ACTION</literal> is a deferred check. In MySQL,
- foreign key constraints are checked immediately, so
- <literal>NO ACTION</literal> and <literal>RESTRICT</literal>
- are the same.)
+ operation for the parent table. Specifying
+ <literal>RESTRICT</literal> (or <literal>NO
+ ACTION</literal>) is the same as omitting the <literal>ON
+ DELETE</literal> or <literal>ON UPDATE</literal> clause.
+ (Some database systems have deferred checks, and <literal>NO
+ ACTION</literal> is a deferred check. In MySQL, foreign key
+ constraints are checked immediately, so <literal>NO
+ ACTION</literal> is the same as
+ <literal>RESTRICT</literal>.)
</para>
</listitem>
@@ -2672,9 +2677,10 @@
<literal>InnoDB</literal> requires indexes on foreign keys and
referenced keys so that foreign key checks can be fast and not
require a table scan. The index on the foreign key is created
- automatically. This is in contrast to some older versions, in
- which indexes had to be created explicitly or the creation of
- foreign key constraints would fail.
+ automatically if no index on the key columns exists. This is in
+ contrast to some older versions, in which indexes had to be
+ created explicitly or the creation of foreign key constraints
+ would fail.
</para>
<para>
@@ -2690,14 +2696,59 @@
server.
</para>
- <note>
+ <important>
<para>
- <literal>InnoDB</literal> does not check foreign key
- constraints on those foreign key or referenced key values that
- contain a <literal>NULL</literal> column.
+ For users familiar with the ANSI/ISO SQL Standard, please note
+ that no storage engine, including <literal>InnoDB</literal>,
+ recognizes or enforces the <literal>MATCH</literal> clause
+ used in referential integrity constraint definitions. Use of
+ an explicit <literal>MATCH</literal> clause will not have the
+ specified effect, and also causes <literal>ON DELETE</literal>
+ and <literal>ON UPDATE</literal> clauses to be ignored. For
+ these reasons, specifying <literal>MATCH</literal> should be
+ avoided.
</para>
- </note>
+ <para>
+ The <literal>MATCH</literal> clause in the SQL standard
+ controls how <literal>NULL</literal> values in a composite
+ (multiple-column) foreign key are handled when comparing to a
+ primary key. <literal>InnoDB</literal> essentially implements
+ the semantics defined by <literal>MATCH NONE</literal>, which
+ allow a foreign key to be all or partially
+ <literal>NULL</literal>. In that case, the (child table) row
+ containing such a foreign key is allowed to be inserted, and
+ does not match any row in the referenced (parent) table. It is
+ possible to implement other semantics using triggers.
+ </para>
+
+ <para>
+ Additionally, MySQL and <literal>InnoDB</literal> require that
+ the referenced columns be indexed for performance. However,
+ the system does not enforce a requirement that the referenced
+ columns be <literal>UNIQUE</literal> or be declared
+ <literal>NOT NULL</literal>. The handling of foreign key
+ references to non-unique keys or keys that contain
+ <literal>NULL</literal> values are not well defined for
+ operations such as <literal>UPDATE</literal> or
+ <literal>DELETE CASCADE</literal>. You are advised to use
+ foreign keys that reference only <literal>UNIQUE</literal> and
+ <literal>NOT NULL</literal> keys.
+ </para>
+
+ <para>
+ Furthermore, <literal>InnoDB</literal> does not recognize or
+ support <quote>inline <literal>REFERENCES</literal>
+ specifications</quote>, as defined in the SQL standard where
+ the references are defined as part of the column
+ specification. <literal>InnoDB</literal> accepts
+ <literal>REFERENCES</literal> clauses only when specified as
+ part of a separate <literal>FOREIGN KEY</literal>
+ specification. All other engines ignore all foreign key
+ specifications.
+ </para>
+ </important>
+
<note>
<para>
Currently, triggers are not activated by cascaded foreign key
Modified: trunk/refman-5.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.0/sql-syntax-data-definition.xml 2008-07-29 16:51:12 UTC (rev 11423)
+++ trunk/refman-5.0/sql-syntax-data-definition.xml 2008-07-29 16:51:23 UTC (rev 11424)
Changed blocks: 1, Lines Added: 11, Lines Deleted: 3; 1429 bytes
@@ -2351,9 +2351,17 @@
note that no storage engine, including
<literal>InnoDB</literal>, recognizes or enforces the
<literal>MATCH</literal> clause used in referential
- integrity constraint definitions. The
- <literal>MATCH</literal> clause in the SQL standard controls
- how <literal>NULL</literal> values in a composite
+ integrity constraint definitions. Use of an explicit
+ <literal>MATCH</literal> clause will not have the specified
+ effect, and also causes <literal>ON DELETE</literal> and
+ <literal>ON UPDATE</literal> clauses to be ignored. For
+ these reasons, specifying <literal>MATCH</literal> should be
+ avoided.
+ </para>
+
+ <para>
+ The <literal>MATCH</literal> clause in the SQL standard
+ controls how <literal>NULL</literal> values in a composite
(multiple-column) foreign key are handled when comparing to
a primary key. <literal>InnoDB</literal> essentially
implements the semantics defined by <literal>MATCH
Modified: trunk/refman-5.1/se-innodb-core.xml
===================================================================
--- trunk/refman-5.1/se-innodb-core.xml 2008-07-29 16:51:12 UTC (rev 11423)
+++ trunk/refman-5.1/se-innodb-core.xml 2008-07-29 16:51:23 UTC (rev 11424)
Changed blocks: 6, Lines Added: 71, Lines Deleted: 20; 6637 bytes
@@ -3145,8 +3145,8 @@
<remark role="help-description-begin"/>
<para>
- <literal>InnoDB</literal> also supports foreign key constraints.
- The syntax for a foreign key constraint definition in
+ <literal>InnoDB</literal> supports foreign key constraints. The
+ syntax for a foreign key constraint definition in
<literal>InnoDB</literal> looks like this:
</para>
@@ -3211,7 +3211,9 @@
<listitem>
<para>
- In the referenced table, there must be an index where the
+ <literal>InnoDB</literal> allows a foreign key to reference
+ any index column or group of columns. However, in the
+ referenced table, there must be an index where the
referenced columns are listed as the
<emphasis>first</emphasis> columns in the same order.
</para>
@@ -3254,7 +3256,9 @@
KEY</literal> clause. When the user attempts to delete or update
a row from a parent table, and there are one or more matching
rows in the child table, <literal>InnoDB</literal> supports five
- options regarding the action to be taken:
+ options regarding the action to be taken. If <literal>ON
+ DELETE</literal> or <literal>ON UPDATE</literal> are not
+ specified, the default action is <literal>RESTRICT</literal>.
</para>
<itemizedlist>
@@ -3310,14 +3314,15 @@
<listitem>
<para>
<literal>RESTRICT</literal>: Rejects the delete or update
- operation for the parent table. <literal>NO ACTION</literal>
- and <literal>RESTRICT</literal> are the same as omitting the
- <literal>ON DELETE</literal> or <literal>ON UPDATE</literal>
- clause. (Some database systems have deferred checks, and
- <literal>NO ACTION</literal> is a deferred check. In MySQL,
- foreign key constraints are checked immediately, so
- <literal>NO ACTION</literal> and <literal>RESTRICT</literal>
- are the same.)
+ operation for the parent table. Specifying
+ <literal>RESTRICT</literal> (or <literal>NO
+ ACTION</literal>) is the same as omitting the <literal>ON
+ DELETE</literal> or <literal>ON UPDATE</literal> clause.
+ (Some database systems have deferred checks, and <literal>NO
+ ACTION</literal> is a deferred check. In MySQL, foreign key
+ constraints are checked immediately, so <literal>NO
+ ACTION</literal> is the same as
+ <literal>RESTRICT</literal>.)
</para>
</listitem>
@@ -3344,9 +3349,10 @@
<literal>InnoDB</literal> requires indexes on foreign keys and
referenced keys so that foreign key checks can be fast and not
require a table scan. The index on the foreign key is created
- automatically. This is in contrast to some older versions, in
- which indexes had to be created explicitly or the creation of
- foreign key constraints would fail.
+ automatically if no index on the key columns exists. This is in
+ contrast to some older versions, in which indexes had to be
+ created explicitly or the creation of foreign key constraints
+ would fail.
</para>
<para>
@@ -3362,14 +3368,59 @@
server.
</para>
- <note>
+ <important>
<para>
- <literal>InnoDB</literal> does not check foreign key
- constraints on those foreign key or referenced key values that
- contain a <literal>NULL</literal> column.
+ For users familiar with the ANSI/ISO SQL Standard, please note
+ that no storage engine, including <literal>InnoDB</literal>,
+ recognizes or enforces the <literal>MATCH</literal> clause
+ used in referential integrity constraint definitions. Use of
+ an explicit <literal>MATCH</literal> clause will not have the
+ specified effect, and also causes <literal>ON DELETE</literal>
+ and <literal>ON UPDATE</literal> clauses to be ignored. For
+ these reasons, specifying <literal>MATCH</literal> should be
+ avoided.
</para>
- </note>
+ <para>
+ The <literal>MATCH</literal> clause in the SQL standard
+ controls how <literal>NULL</literal> values in a composite
+ (multiple-column) foreign key are handled when comparing to a
+ primary key. <literal>InnoDB</literal> essentially implements
+ the semantics defined by <literal>MATCH NONE</literal>, which
+ allow a foreign key to be all or partially
+ <literal>NULL</literal>. In that case, the (child table) row
+ containing such a foreign key is allowed to be inserted, and
+ does not match any row in the referenced (parent) table. It is
+ possible to implement other semantics using triggers.
+ </para>
+
+ <para>
+ Additionally, MySQL and <literal>InnoDB</literal> require that
+ the referenced columns be indexed for performance. However,
+ the system does not enforce a requirement that the referenced
+ columns be <literal>UNIQUE</literal> or be declared
+ <literal>NOT NULL</literal>. The handling of foreign key
+ references to non-unique keys or keys that contain
+ <literal>NULL</literal> values are not well defined for
+ operations such as <literal>UPDATE</literal> or
+ <literal>DELETE CASCADE</literal>. You are advised to use
+ foreign keys that reference only <literal>UNIQUE</literal> and
+ <literal>NOT NULL</literal> keys.
+ </para>
+
+ <para>
+ Furthermore, <literal>InnoDB</literal> does not recognize or
+ support <quote>inline <literal>REFERENCES</literal>
+ specifications</quote>, as defined in the SQL standard where
+ the references are defined as part of the column
+ specification. <literal>InnoDB</literal> accepts
+ <literal>REFERENCES</literal> clauses only when specified as
+ part of a separate <literal>FOREIGN KEY</literal>
+ specification. All other engines ignore all foreign key
+ specifications.
+ </para>
+ </important>
+
<note>
<para>
Currently, triggers are not activated by cascaded foreign key
Modified: trunk/refman-5.1/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.1/sql-syntax-data-definition.xml 2008-07-29 16:51:12 UTC (rev 11423)
+++ trunk/refman-5.1/sql-syntax-data-definition.xml 2008-07-29 16:51:23 UTC (rev 11424)
Changed blocks: 1, Lines Added: 11, Lines Deleted: 3; 1429 bytes
@@ -3802,9 +3802,17 @@
note that no storage engine, including
<literal>InnoDB</literal>, recognizes or enforces the
<literal>MATCH</literal> clause used in referential
- integrity constraint definitions. The
- <literal>MATCH</literal> clause in the SQL standard controls
- how <literal>NULL</literal> values in a composite
+ integrity constraint definitions. Use of an explicit
+ <literal>MATCH</literal> clause will not have the specified
+ effect, and also causes <literal>ON DELETE</literal> and
+ <literal>ON UPDATE</literal> clauses to be ignored. For
+ these reasons, specifying <literal>MATCH</literal> should be
+ avoided.
+ </para>
+
+ <para>
+ The <literal>MATCH</literal> clause in the SQL standard
+ controls how <literal>NULL</literal> values in a composite
(multiple-column) foreign key are handled when comparing to
a primary key. <literal>InnoDB</literal> essentially
implements the semantics defined by <literal>MATCH
Modified: trunk/refman-6.0/se-innodb-core.xml
===================================================================
--- trunk/refman-6.0/se-innodb-core.xml 2008-07-29 16:51:12 UTC (rev 11423)
+++ trunk/refman-6.0/se-innodb-core.xml 2008-07-29 16:51:23 UTC (rev 11424)
Changed blocks: 6, Lines Added: 71, Lines Deleted: 20; 6637 bytes
@@ -3050,8 +3050,8 @@
<remark role="help-description-begin"/>
<para>
- <literal>InnoDB</literal> also supports foreign key constraints.
- The syntax for a foreign key constraint definition in
+ <literal>InnoDB</literal> supports foreign key constraints. The
+ syntax for a foreign key constraint definition in
<literal>InnoDB</literal> looks like this:
</para>
@@ -3116,7 +3116,9 @@
<listitem>
<para>
- In the referenced table, there must be an index where the
+ <literal>InnoDB</literal> allows a foreign key to reference
+ any index column or group of columns. However, in the
+ referenced table, there must be an index where the
referenced columns are listed as the
<emphasis>first</emphasis> columns in the same order.
</para>
@@ -3159,7 +3161,9 @@
KEY</literal> clause. When the user attempts to delete or update
a row from a parent table, and there are one or more matching
rows in the child table, <literal>InnoDB</literal> supports five
- options regarding the action to be taken:
+ options regarding the action to be taken. If <literal>ON
+ DELETE</literal> or <literal>ON UPDATE</literal> are not
+ specified, the default action is <literal>RESTRICT</literal>.
</para>
<itemizedlist>
@@ -3215,14 +3219,15 @@
<listitem>
<para>
<literal>RESTRICT</literal>: Rejects the delete or update
- operation for the parent table. <literal>NO ACTION</literal>
- and <literal>RESTRICT</literal> are the same as omitting the
- <literal>ON DELETE</literal> or <literal>ON UPDATE</literal>
- clause. (Some database systems have deferred checks, and
- <literal>NO ACTION</literal> is a deferred check. In MySQL,
- foreign key constraints are checked immediately, so
- <literal>NO ACTION</literal> and <literal>RESTRICT</literal>
- are the same.)
+ operation for the parent table. Specifying
+ <literal>RESTRICT</literal> (or <literal>NO
+ ACTION</literal>) is the same as omitting the <literal>ON
+ DELETE</literal> or <literal>ON UPDATE</literal> clause.
+ (Some database systems have deferred checks, and <literal>NO
+ ACTION</literal> is a deferred check. In MySQL, foreign key
+ constraints are checked immediately, so <literal>NO
+ ACTION</literal> is the same as
+ <literal>RESTRICT</literal>.)
</para>
</listitem>
@@ -3249,9 +3254,10 @@
<literal>InnoDB</literal> requires indexes on foreign keys and
referenced keys so that foreign key checks can be fast and not
require a table scan. The index on the foreign key is created
- automatically. This is in contrast to some older versions, in
- which indexes had to be created explicitly or the creation of
- foreign key constraints would fail.
+ automatically if no index on the key columns exists. This is in
+ contrast to some older versions, in which indexes had to be
+ created explicitly or the creation of foreign key constraints
+ would fail.
</para>
<para>
@@ -3267,14 +3273,59 @@
server.
</para>
- <note>
+ <important>
<para>
- <literal>InnoDB</literal> does not check foreign key
- constraints on those foreign key or referenced key values that
- contain a <literal>NULL</literal> column.
+ For users familiar with the ANSI/ISO SQL Standard, please note
+ that no storage engine, including <literal>InnoDB</literal>,
+ recognizes or enforces the <literal>MATCH</literal> clause
+ used in referential integrity constraint definitions. Use of
+ an explicit <literal>MATCH</literal> clause will not have the
+ specified effect, and also causes <literal>ON DELETE</literal>
+ and <literal>ON UPDATE</literal> clauses to be ignored. For
+ these reasons, specifying <literal>MATCH</literal> should be
+ avoided.
</para>
- </note>
+ <para>
+ The <literal>MATCH</literal> clause in the SQL standard
+ controls how <literal>NULL</literal> values in a composite
+ (multiple-column) foreign key are handled when comparing to a
+ primary key. <literal>InnoDB</literal> essentially implements
+ the semantics defined by <literal>MATCH NONE</literal>, which
+ allow a foreign key to be all or partially
+ <literal>NULL</literal>. In that case, the (child table) row
+ containing such a foreign key is allowed to be inserted, and
+ does not match any row in the referenced (parent) table. It is
+ possible to implement other semantics using triggers.
+ </para>
+
+ <para>
+ Additionally, MySQL and <literal>InnoDB</literal> require that
+ the referenced columns be indexed for performance. However,
+ the system does not enforce a requirement that the referenced
+ columns be <literal>UNIQUE</literal> or be declared
+ <literal>NOT NULL</literal>. The handling of foreign key
+ references to non-unique keys or keys that contain
+ <literal>NULL</literal> values are not well defined for
+ operations such as <literal>UPDATE</literal> or
+ <literal>DELETE CASCADE</literal>. You are advised to use
+ foreign keys that reference only <literal>UNIQUE</literal> and
+ <literal>NOT NULL</literal> keys.
+ </para>
+
+ <para>
+ Furthermore, <literal>InnoDB</literal> does not recognize or
+ support <quote>inline <literal>REFERENCES</literal>
+ specifications</quote>, as defined in the SQL standard where
+ the references are defined as part of the column
+ specification. <literal>InnoDB</literal> accepts
+ <literal>REFERENCES</literal> clauses only when specified as
+ part of a separate <literal>FOREIGN KEY</literal>
+ specification. All other engines ignore all foreign key
+ specifications.
+ </para>
+ </important>
+
<note>
<para>
Currently, triggers are not activated by cascaded foreign key
Modified: trunk/refman-6.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-6.0/sql-syntax-data-definition.xml 2008-07-29 16:51:12 UTC (rev 11423)
+++ trunk/refman-6.0/sql-syntax-data-definition.xml 2008-07-29 16:51:23 UTC (rev 11424)
Changed blocks: 1, Lines Added: 11, Lines Deleted: 3; 1429 bytes
@@ -3519,9 +3519,17 @@
note that no storage engine, including
<literal>InnoDB</literal>, recognizes or enforces the
<literal>MATCH</literal> clause used in referential
- integrity constraint definitions. The
- <literal>MATCH</literal> clause in the SQL standard controls
- how <literal>NULL</literal> values in a composite
+ integrity constraint definitions. Use of an explicit
+ <literal>MATCH</literal> clause will not have the specified
+ effect, and also causes <literal>ON DELETE</literal> and
+ <literal>ON UPDATE</literal> clauses to be ignored. For
+ these reasons, specifying <literal>MATCH</literal> should be
+ avoided.
+ </para>
+
+ <para>
+ The <literal>MATCH</literal> clause in the SQL standard
+ controls how <literal>NULL</literal> values in a composite
(multiple-column) foreign key are handled when comparing to
a primary key. <literal>InnoDB</literal> essentially
implements the semantics defined by <literal>MATCH
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r11424 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-6.0 | paul.dubois | 29 Jul |
