Author: paul
Date: 2008-07-28 22:52:07 +0200 (Mon, 28 Jul 2008)
New Revision: 11399
Log:
r33026@frost: paul | 2008-07-28 15:02:19 -0500
Information about InnoDB handling of foreign keys, MATCH. (Ken)
Modified:
trunk/refman-4.1/sql-syntax-data-definition.xml
trunk/refman-5.0/sql-syntax-data-definition.xml
trunk/refman-5.1/sql-syntax-data-definition.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:33025
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:32506
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:33026
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:32506
Modified: trunk/refman-4.1/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-4.1/sql-syntax-data-definition.xml 2008-07-28 20:51:59 UTC (rev 11398)
+++ trunk/refman-4.1/sql-syntax-data-definition.xml 2008-07-28 20:52:07 UTC (rev 11399)
Changed blocks: 1, Lines Added: 42, Lines Deleted: 6; 3189 bytes
@@ -2287,13 +2287,49 @@
<important>
<para>
- The inline <literal>REFERENCES</literal> specifications
- where the references are defined as part of the column
- specification are silently ignored by
- <literal>InnoDB</literal>. InnoDB only accepts
- <literal>REFERENCES</literal> clauses when specified as part
- of a separate <literal>FOREIGN KEY</literal> specification.
+ 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. 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>
</listitem>
Modified: trunk/refman-5.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.0/sql-syntax-data-definition.xml 2008-07-28 20:51:59 UTC (rev 11398)
+++ trunk/refman-5.0/sql-syntax-data-definition.xml 2008-07-28 20:52:07 UTC (rev 11399)
Changed blocks: 1, Lines Added: 42, Lines Deleted: 6; 3189 bytes
@@ -2347,13 +2347,49 @@
<important>
<para>
- The inline <literal>REFERENCES</literal> specifications
- where the references are defined as part of the column
- specification are silently ignored by
- <literal>InnoDB</literal>. InnoDB only accepts
- <literal>REFERENCES</literal> clauses when specified as part
- of a separate <literal>FOREIGN KEY</literal> specification.
+ 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. 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>
</listitem>
Modified: trunk/refman-5.1/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.1/sql-syntax-data-definition.xml 2008-07-28 20:51:59 UTC (rev 11398)
+++ trunk/refman-5.1/sql-syntax-data-definition.xml 2008-07-28 20:52:07 UTC (rev 11399)
Changed blocks: 1, Lines Added: 42, Lines Deleted: 6; 3186 bytes
@@ -3798,13 +3798,49 @@
<important>
<para>
- The inline <literal>REFERENCES</literal> specifications
- where the references are defined as part of the column
- specification are silently ignored by
- <literal>InnoDB</literal>. InnoDB only accepts
- <literal>REFERENCES</literal> clauses when specified as part
- of a separate <literal>FOREIGN KEY</literal> specification.
+ 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. 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>
Modified: trunk/refman-6.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-6.0/sql-syntax-data-definition.xml 2008-07-28 20:51:59 UTC (rev 11398)
+++ trunk/refman-6.0/sql-syntax-data-definition.xml 2008-07-28 20:52:07 UTC (rev 11399)
Changed blocks: 1, Lines Added: 42, Lines Deleted: 6; 3186 bytes
@@ -3515,13 +3515,49 @@
<important>
<para>
- The inline <literal>REFERENCES</literal> specifications
- where the references are defined as part of the column
- specification are silently ignored by
- <literal>InnoDB</literal>. InnoDB only accepts
- <literal>REFERENCES</literal> clauses when specified as part
- of a separate <literal>FOREIGN KEY</literal> specification.
+ 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. 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>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r11399 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-6.0 | paul.dubois | 28 Jul |
