Author: paul
Date: 2008-11-05 17:56:06 +0100 (Wed, 05 Nov 2008)
New Revision: 12292
Log:
r35317@frost: paul | 2008-11-05 10:56:40 -0500
Reorganize material within section
Modified:
trunk/refman-4.1/se-innodb-core.xml
trunk/refman-5.0/se-innodb-core.xml
trunk/refman-5.1/se-innodb-core.xml
trunk/refman-6.0/se-innodb-core.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:39854
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:35314
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:34100
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:39854
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:35317
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:34100
Modified: trunk/refman-4.1/se-innodb-core.xml
===================================================================
--- trunk/refman-4.1/se-innodb-core.xml 2008-11-05 16:48:47 UTC (rev 12291)
+++ trunk/refman-4.1/se-innodb-core.xml 2008-11-05 16:56:06 UTC (rev 12292)
Changed blocks: 3, Lines Added: 134, Lines Deleted: 138; 14659 bytes
@@ -2500,10 +2500,6 @@
<itemizedlist>
- <remark role="todo">
- Rewrite following paragraph to make clearer.
- </remark>
-
<listitem>
<para>
<literal>CASCADE</literal>: Delete or update the row from
@@ -2594,140 +2590,6 @@
</para>
<para>
- If MySQL reports an error number 1005 from a
- <literal role="stmt">CREATE TABLE</literal> statement, and the
- error message refers to errno 150, table creation failed because
- a foreign key constraint was not correctly formed. Similarly, if
- an <literal role="stmt">ALTER TABLE</literal> fails and it
- refers to errno 150, that means a foreign key definition would
- be incorrectly formed for the altered table. Starting from MySQL
- 4.0.13, you can use <literal role="stmt">SHOW INNODB
- STATUS</literal> to display a detailed explanation of the latest
- <literal>InnoDB</literal> foreign key error in the server.
- </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>
-
- <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. 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 SIMPLE</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>
- 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 is not well defined for
- operations such as <literal role="stmt">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. For other storage engines, MySQL Server parses
- and ignores 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
- <literal>DB_ROW_ID</literal>, <literal>DB_TRX_ID</literal>,
- <literal>DB_ROLL_PTR</literal> and
- <literal>DB_MIX_ID</literal>). In versions of MySQL before
- 4.1.19 this would cause a crash, since 4.1.19 the server will
- report error 1005 and refers to <literal>errno</literal> -1 in
- the error message.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- If there are several rows in the parent table that have the same
- referenced key value, <literal>InnoDB</literal> acts in foreign
- key checks as if the other parent rows with the same key value
- do not exist. For example, if you have defined a
- <literal>RESTRICT</literal> type constraint, and there is a
- child row with several parent rows, <literal>InnoDB</literal>
- does not allow the deletion of any of those parent rows.
- </para>
-
- <para>
- <literal>InnoDB</literal> performs cascading operations through
- a depth-first algorithm, based on records in the indexes
- corresponding to the foreign key constraints.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>: A
- <literal>FOREIGN KEY</literal> constraint that references a
- non-<literal>UNIQUE</literal> key is not standard SQL. It is an
- <literal>InnoDB</literal> extension to standard SQL.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- If <literal>ON UPDATE CASCADE</literal> or <literal>ON UPDATE
- SET NULL</literal> recurses to update the <emphasis>same
- table</emphasis> it has previously updated during the cascade,
- it acts like <literal>RESTRICT</literal>. This means that you
- cannot use self-referential <literal>ON UPDATE CASCADE</literal>
- or <literal>ON UPDATE SET NULL</literal> operations. This is to
- prevent infinite loops resulting from cascaded updates. A
- self-referential <literal>ON DELETE SET NULL</literal>, on the
- other hand, is possible from 4.0.13. A self-referential
- <literal>ON DELETE CASCADE</literal> has been possible since
- <literal>ON DELETE</literal> was implemented. Since 4.0.21,
- cascading operations may not be nested more than 15 levels.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- Like MySQL in general, in an SQL statement that inserts,
- deletes, or updates many rows, <literal>InnoDB</literal> checks
- <literal>UNIQUE</literal> and <literal>FOREIGN KEY</literal>
- constraints row-by-row. According to the SQL standard, the
- default behavior should be deferred checking. That is,
- constraints are only checked after the
- <emphasis role="bold">whole SQL statement</emphasis> has been
- processed. Until <literal>InnoDB</literal> implements deferred
- constraint checking, some things will be impossible, such as
- deleting a record that refers to itself via a foreign key.
- </para>
-
- <para>
Here is a simple example that relates <literal>parent</literal>
and <literal>child</literal> tables through a single-column
foreign key:
@@ -2993,6 +2855,140 @@
1005 and refers to errno 150 in the error message.
</para>
+ <para>
+ If MySQL reports an error number 1005 from a
+ <literal role="stmt">CREATE TABLE</literal> statement, and the
+ error message refers to errno 150, table creation failed because
+ a foreign key constraint was not correctly formed. Similarly, if
+ an <literal role="stmt">ALTER TABLE</literal> fails and it
+ refers to errno 150, that means a foreign key definition would
+ be incorrectly formed for the altered table. Starting from MySQL
+ 4.0.13, you can use <literal role="stmt">SHOW INNODB
+ STATUS</literal> to display a detailed explanation of the latest
+ <literal>InnoDB</literal> foreign key error in the server.
+ </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>
+
+ <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. 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 SIMPLE</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>
+ 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 is not well defined for
+ operations such as <literal role="stmt">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. For other storage engines, MySQL Server parses
+ and ignores 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
+ <literal>DB_ROW_ID</literal>, <literal>DB_TRX_ID</literal>,
+ <literal>DB_ROLL_PTR</literal> and
+ <literal>DB_MIX_ID</literal>). In versions of MySQL before
+ 4.1.19 this would cause a crash, since 4.1.19 the server will
+ report error 1005 and refers to <literal>errno</literal> -1 in
+ the error message.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ If there are several rows in the parent table that have the same
+ referenced key value, <literal>InnoDB</literal> acts in foreign
+ key checks as if the other parent rows with the same key value
+ do not exist. For example, if you have defined a
+ <literal>RESTRICT</literal> type constraint, and there is a
+ child row with several parent rows, <literal>InnoDB</literal>
+ does not allow the deletion of any of those parent rows.
+ </para>
+
+ <para>
+ <literal>InnoDB</literal> performs cascading operations through
+ a depth-first algorithm, based on records in the indexes
+ corresponding to the foreign key constraints.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>: A
+ <literal>FOREIGN KEY</literal> constraint that references a
+ non-<literal>UNIQUE</literal> key is not standard SQL. It is an
+ <literal>InnoDB</literal> extension to standard SQL.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ If <literal>ON UPDATE CASCADE</literal> or <literal>ON UPDATE
+ SET NULL</literal> recurses to update the <emphasis>same
+ table</emphasis> it has previously updated during the cascade,
+ it acts like <literal>RESTRICT</literal>. This means that you
+ cannot use self-referential <literal>ON UPDATE CASCADE</literal>
+ or <literal>ON UPDATE SET NULL</literal> operations. This is to
+ prevent infinite loops resulting from cascaded updates. A
+ self-referential <literal>ON DELETE SET NULL</literal>, on the
+ other hand, is possible from 4.0.13. A self-referential
+ <literal>ON DELETE CASCADE</literal> has been possible since
+ <literal>ON DELETE</literal> was implemented. Since 4.0.21,
+ cascading operations may not be nested more than 15 levels.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ Like MySQL in general, in an SQL statement that inserts,
+ deletes, or updates many rows, <literal>InnoDB</literal> checks
+ <literal>UNIQUE</literal> and <literal>FOREIGN KEY</literal>
+ constraints row-by-row. According to the SQL standard, the
+ default behavior should be deferred checking. That is,
+ constraints are only checked after the
+ <emphasis role="bold">whole SQL statement</emphasis> has been
+ processed. Until <literal>InnoDB</literal> implements deferred
+ constraint checking, some things will be impossible, such as
+ deleting a record that refers to itself via a foreign key.
+ </para>
+
</section>
<section id="innodb-and-mysql-replication">
Modified: trunk/refman-5.0/se-innodb-core.xml
===================================================================
--- trunk/refman-5.0/se-innodb-core.xml 2008-11-05 16:48:47 UTC (rev 12291)
+++ trunk/refman-5.0/se-innodb-core.xml 2008-11-05 16:56:06 UTC (rev 12292)
Changed blocks: 4, Lines Added: 126, Lines Deleted: 130; 13583 bytes
@@ -2618,10 +2618,6 @@
<itemizedlist>
- <remark role="todo">
- Rewrite following paragraph to make clearer.
- </remark>
-
<listitem>
<para>
<literal>CASCADE</literal>: Delete or update the row from
@@ -2633,6 +2629,13 @@
act on the same column in the parent table or in the child
table.
</para>
+
+ <note>
+ <para>
+ Currently, triggers are not activated by cascaded foreign
+ key actions.
+ </para>
+ </note>
</listitem>
<listitem>
@@ -2711,132 +2714,6 @@
</para>
<para>
- If MySQL reports an error number 1005 from a
- <literal role="stmt">CREATE TABLE</literal> statement, and the
- error message refers to errno 150, table creation failed because
- a foreign key constraint was not correctly formed. Similarly, if
- an <literal role="stmt">ALTER TABLE</literal> fails and it
- refers to errno 150, that means a foreign key definition would
- be incorrectly formed for the altered table. You can use
- <literal>SHOW ENGINE INNODB STATUS</literal> to display a
- detailed explanation of the most recent
- <literal>InnoDB</literal> foreign key error in the server.
- </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>
-
- <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 SIMPLE</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 is not well defined for
- operations such as <literal role="stmt">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. For other storage engines, MySQL Server parses
- and ignores foreign key specifications.
- </para>
- </important>
-
- <note>
- <para>
- Currently, triggers are not activated by cascaded foreign key
- actions.
- </para>
- </note>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- If there are several rows in the parent table that have the same
- referenced key value, <literal>InnoDB</literal> acts in foreign
- key checks as if the other parent rows with the same key value
- do not exist. For example, if you have defined a
- <literal>RESTRICT</literal> type constraint, and there is a
- child row with several parent rows, <literal>InnoDB</literal>
- does not allow the deletion of any of those parent rows.
- </para>
-
- <para>
- <literal>InnoDB</literal> performs cascading operations through
- a depth-first algorithm, based on records in the indexes
- corresponding to the foreign key constraints.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>: A
- <literal>FOREIGN KEY</literal> constraint that references a
- non-<literal>UNIQUE</literal> key is not standard SQL. It is an
- <literal>InnoDB</literal> extension to standard SQL.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- If <literal>ON UPDATE CASCADE</literal> or <literal>ON UPDATE
- SET NULL</literal> recurses to update the <emphasis>same
- table</emphasis> it has previously updated during the cascade,
- it acts like <literal>RESTRICT</literal>. This means that you
- cannot use self-referential <literal>ON UPDATE CASCADE</literal>
- or <literal>ON UPDATE SET NULL</literal> operations. This is to
- prevent infinite loops resulting from cascaded updates. A
- self-referential <literal>ON DELETE SET NULL</literal>, on the
- other hand, is possible, as is a self-referential <literal>ON
- DELETE CASCADE</literal>. Cascading operations may not be nested
- more than 15 levels deep.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- Like MySQL in general, in an SQL statement that inserts,
- deletes, or updates many rows, <literal>InnoDB</literal> checks
- <literal>UNIQUE</literal> and <literal>FOREIGN KEY</literal>
- constraints row-by-row. According to the SQL standard, the
- default behavior should be deferred checking. That is,
- constraints are only checked after the <emphasis>entire SQL
- statement</emphasis> has been processed. Until
- <literal>InnoDB</literal> implements deferred constraint
- checking, some things will be impossible, such as deleting a
- record that refers to itself via a foreign key.
- </para>
-
- <para>
Here is a simple example that relates <literal>parent</literal>
and <literal>child</literal> tables through a single-column
foreign key:
@@ -3078,6 +2955,125 @@
1005 and refers to errno 150 in the error message.
</para>
+ <para>
+ If MySQL reports an error number 1005 from a
+ <literal role="stmt">CREATE TABLE</literal> statement, and the
+ error message refers to errno 150, table creation failed because
+ a foreign key constraint was not correctly formed. Similarly, if
+ an <literal role="stmt">ALTER TABLE</literal> fails and it
+ refers to errno 150, that means a foreign key definition would
+ be incorrectly formed for the altered table. You can use
+ <literal>SHOW ENGINE INNODB STATUS</literal> to display a
+ detailed explanation of the most recent
+ <literal>InnoDB</literal> foreign key error in the server.
+ </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>
+
+ <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 SIMPLE</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 is not well defined for
+ operations such as <literal role="stmt">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. For other storage engines, MySQL Server parses
+ and ignores foreign key specifications.
+ </para>
+ </important>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ If there are several rows in the parent table that have the same
+ referenced key value, <literal>InnoDB</literal> acts in foreign
+ key checks as if the other parent rows with the same key value
+ do not exist. For example, if you have defined a
+ <literal>RESTRICT</literal> type constraint, and there is a
+ child row with several parent rows, <literal>InnoDB</literal>
+ does not allow the deletion of any of those parent rows.
+ </para>
+
+ <para>
+ <literal>InnoDB</literal> performs cascading operations through
+ a depth-first algorithm, based on records in the indexes
+ corresponding to the foreign key constraints.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>: A
+ <literal>FOREIGN KEY</literal> constraint that references a
+ non-<literal>UNIQUE</literal> key is not standard SQL. It is an
+ <literal>InnoDB</literal> extension to standard SQL.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ If <literal>ON UPDATE CASCADE</literal> or <literal>ON UPDATE
+ SET NULL</literal> recurses to update the <emphasis>same
+ table</emphasis> it has previously updated during the cascade,
+ it acts like <literal>RESTRICT</literal>. This means that you
+ cannot use self-referential <literal>ON UPDATE CASCADE</literal>
+ or <literal>ON UPDATE SET NULL</literal> operations. This is to
+ prevent infinite loops resulting from cascaded updates. A
+ self-referential <literal>ON DELETE SET NULL</literal>, on the
+ other hand, is possible, as is a self-referential <literal>ON
+ DELETE CASCADE</literal>. Cascading operations may not be nested
+ more than 15 levels deep.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ Like MySQL in general, in an SQL statement that inserts,
+ deletes, or updates many rows, <literal>InnoDB</literal> checks
+ <literal>UNIQUE</literal> and <literal>FOREIGN KEY</literal>
+ constraints row-by-row. According to the SQL standard, the
+ default behavior should be deferred checking. That is,
+ constraints are only checked after the <emphasis>entire SQL
+ statement</emphasis> has been processed. Until
+ <literal>InnoDB</literal> implements deferred constraint
+ checking, some things will be impossible, such as deleting a
+ record that refers to itself via a foreign key.
+ </para>
+
</section>
<section id="innodb-and-mysql-replication">
Modified: trunk/refman-5.1/se-innodb-core.xml
===================================================================
--- trunk/refman-5.1/se-innodb-core.xml 2008-11-05 16:48:47 UTC (rev 12291)
+++ trunk/refman-5.1/se-innodb-core.xml 2008-11-05 16:56:06 UTC (rev 12292)
Changed blocks: 4, Lines Added: 137, Lines Deleted: 141; 14577 bytes
@@ -3302,10 +3302,6 @@
<itemizedlist>
- <remark role="todo">
- Rewrite following paragraph to make clearer.
- </remark>
-
<listitem>
<para>
<literal>CASCADE</literal>: Delete or update the row from
@@ -3317,6 +3313,13 @@
act on the same column in the parent table or in the child
table.
</para>
+
+ <note>
+ <para>
+ Currently, triggers are not activated by cascaded foreign
+ key actions.
+ </para>
+ </note>
</listitem>
<listitem>
@@ -3395,143 +3398,6 @@
</para>
<para>
- If MySQL reports an error number 1005 from a
- <literal role="stmt">CREATE TABLE</literal> statement, and the
- error message refers to errno 150, table creation failed because
- a foreign key constraint was not correctly formed. Similarly, if
- an <literal role="stmt">ALTER TABLE</literal> fails and it
- refers to errno 150, that means a foreign key definition would
- be incorrectly formed for the altered table. You can use
- <literal>SHOW ENGINE INNODB STATUS</literal> to display a
- detailed explanation of the most recent
- <literal>InnoDB</literal> foreign key error in the server.
- </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>
-
- <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 SIMPLE</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 is not well defined for
- operations such as <literal role="stmt">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. For other storage engines, MySQL Server parses
- and ignores foreign key specifications.
- </para>
- </important>
-
- <note>
- <para>
- Currently, triggers are not activated by cascaded foreign key
- actions.
- </para>
- </note>
-
- <para>
- You cannot create a table with a column name that matches the
- name of an internal InnoDB column (including
- <literal>DB_ROW_ID</literal>, <literal>DB_TRX_ID</literal>,
- <literal>DB_ROLL_PTR</literal> and
- <literal>DB_MIX_ID</literal>). In versions of MySQL before
- 5.1.10 this would cause a crash, since 5.1.10 the server will
- report error 1005 and refers to <literal>errno</literal> -1 in
- the error message.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- If there are several rows in the parent table that have the same
- referenced key value, <literal>InnoDB</literal> acts in foreign
- key checks as if the other parent rows with the same key value
- do not exist. For example, if you have defined a
- <literal>RESTRICT</literal> type constraint, and there is a
- child row with several parent rows, <literal>InnoDB</literal>
- does not allow the deletion of any of those parent rows.
- </para>
-
- <para>
- <literal>InnoDB</literal> performs cascading operations through
- a depth-first algorithm, based on records in the indexes
- corresponding to the foreign key constraints.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>: A
- <literal>FOREIGN KEY</literal> constraint that references a
- non-<literal>UNIQUE</literal> key is not standard SQL. It is an
- <literal>InnoDB</literal> extension to standard SQL.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- If <literal>ON UPDATE CASCADE</literal> or <literal>ON UPDATE
- SET NULL</literal> recurses to update the <emphasis>same
- table</emphasis> it has previously updated during the cascade,
- it acts like <literal>RESTRICT</literal>. This means that you
- cannot use self-referential <literal>ON UPDATE CASCADE</literal>
- or <literal>ON UPDATE SET NULL</literal> operations. This is to
- prevent infinite loops resulting from cascaded updates. A
- self-referential <literal>ON DELETE SET NULL</literal>, on the
- other hand, is possible, as is a self-referential <literal>ON
- DELETE CASCADE</literal>. Cascading operations may not be nested
- more than 15 levels deep.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- Like MySQL in general, in an SQL statement that inserts,
- deletes, or updates many rows, <literal>InnoDB</literal> checks
- <literal>UNIQUE</literal> and <literal>FOREIGN KEY</literal>
- constraints row-by-row. According to the SQL standard, the
- default behavior should be deferred checking. That is,
- constraints are only checked after the <emphasis>entire SQL
- statement</emphasis> has been processed. Until
- <literal>InnoDB</literal> implements deferred constraint
- checking, some things will be impossible, such as deleting a
- record that refers to itself via a foreign key.
- </para>
-
- <para>
Here is a simple example that relates <literal>parent</literal>
and <literal>child</literal> tables through a single-column
foreign key:
@@ -3773,6 +3639,136 @@
1005 and refers to errno 150 in the error message.
</para>
+ <para>
+ If MySQL reports an error number 1005 from a
+ <literal role="stmt">CREATE TABLE</literal> statement, and the
+ error message refers to errno 150, table creation failed because
+ a foreign key constraint was not correctly formed. Similarly, if
+ an <literal role="stmt">ALTER TABLE</literal> fails and it
+ refers to errno 150, that means a foreign key definition would
+ be incorrectly formed for the altered table. You can use
+ <literal>SHOW ENGINE INNODB STATUS</literal> to display a
+ detailed explanation of the most recent
+ <literal>InnoDB</literal> foreign key error in the server.
+ </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>
+
+ <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 SIMPLE</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 is not well defined for
+ operations such as <literal role="stmt">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. For other storage engines, MySQL Server parses
+ and ignores 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
+ <literal>DB_ROW_ID</literal>, <literal>DB_TRX_ID</literal>,
+ <literal>DB_ROLL_PTR</literal> and
+ <literal>DB_MIX_ID</literal>). In versions of MySQL before
+ 5.1.10 this would cause a crash, since 5.1.10 the server will
+ report error 1005 and refers to <literal>errno</literal> -1 in
+ the error message.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ If there are several rows in the parent table that have the same
+ referenced key value, <literal>InnoDB</literal> acts in foreign
+ key checks as if the other parent rows with the same key value
+ do not exist. For example, if you have defined a
+ <literal>RESTRICT</literal> type constraint, and there is a
+ child row with several parent rows, <literal>InnoDB</literal>
+ does not allow the deletion of any of those parent rows.
+ </para>
+
+ <para>
+ <literal>InnoDB</literal> performs cascading operations through
+ a depth-first algorithm, based on records in the indexes
+ corresponding to the foreign key constraints.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>: A
+ <literal>FOREIGN KEY</literal> constraint that references a
+ non-<literal>UNIQUE</literal> key is not standard SQL. It is an
+ <literal>InnoDB</literal> extension to standard SQL.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ If <literal>ON UPDATE CASCADE</literal> or <literal>ON UPDATE
+ SET NULL</literal> recurses to update the <emphasis>same
+ table</emphasis> it has previously updated during the cascade,
+ it acts like <literal>RESTRICT</literal>. This means that you
+ cannot use self-referential <literal>ON UPDATE CASCADE</literal>
+ or <literal>ON UPDATE SET NULL</literal> operations. This is to
+ prevent infinite loops resulting from cascaded updates. A
+ self-referential <literal>ON DELETE SET NULL</literal>, on the
+ other hand, is possible, as is a self-referential <literal>ON
+ DELETE CASCADE</literal>. Cascading operations may not be nested
+ more than 15 levels deep.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ Like MySQL in general, in an SQL statement that inserts,
+ deletes, or updates many rows, <literal>InnoDB</literal> checks
+ <literal>UNIQUE</literal> and <literal>FOREIGN KEY</literal>
+ constraints row-by-row. According to the SQL standard, the
+ default behavior should be deferred checking. That is,
+ constraints are only checked after the <emphasis>entire SQL
+ statement</emphasis> has been processed. Until
+ <literal>InnoDB</literal> implements deferred constraint
+ checking, some things will be impossible, such as deleting a
+ record that refers to itself via a foreign key.
+ </para>
+
</section>
<section id="innodb-and-mysql-replication">
Modified: trunk/refman-6.0/se-innodb-core.xml
===================================================================
--- trunk/refman-6.0/se-innodb-core.xml 2008-11-05 16:48:47 UTC (rev 12291)
+++ trunk/refman-6.0/se-innodb-core.xml 2008-11-05 16:56:06 UTC (rev 12292)
Changed blocks: 4, Lines Added: 135, Lines Deleted: 139; 14397 bytes
@@ -3203,10 +3203,6 @@
<itemizedlist>
- <remark role="todo">
- Rewrite following paragraph to make clearer.
- </remark>
-
<listitem>
<para>
<literal>CASCADE</literal>: Delete or update the row from
@@ -3218,6 +3214,13 @@
act on the same column in the parent table or in the child
table.
</para>
+
+ <note>
+ <para>
+ Currently, triggers are not activated by cascaded foreign
+ key actions.
+ </para>
+ </note>
</listitem>
<listitem>
@@ -3296,141 +3299,6 @@
</para>
<para>
- If MySQL reports an error number 1005 from a
- <literal role="stmt">CREATE TABLE</literal> statement, and the
- error message refers to errno 150, table creation failed because
- a foreign key constraint was not correctly formed. Similarly, if
- an <literal role="stmt">ALTER TABLE</literal> fails and it
- refers to errno 150, that means a foreign key definition would
- be incorrectly formed for the altered table. You can use
- <literal>SHOW ENGINE INNODB STATUS</literal> to display a
- detailed explanation of the most recent
- <literal>InnoDB</literal> foreign key error in the server.
- </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>
-
- <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 SIMPLE</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 is not well defined for
- operations such as <literal role="stmt">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. For other storage engines, MySQL Server parses
- and ignores foreign key specifications.
- </para>
- </important>
-
- <note>
- <para>
- Currently, triggers are not activated by cascaded foreign key
- actions.
- </para>
- </note>
-
- <para>
- You cannot create a table with a column name that matches the
- name of an internal InnoDB column (including
- <literal>DB_ROW_ID</literal>, <literal>DB_TRX_ID</literal>,
- <literal>DB_ROLL_PTR</literal> and
- <literal>DB_MIX_ID</literal>). The server will report error 1005
- and refers to <literal>errno</literal> -1 in the error message.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- If there are several rows in the parent table that have the same
- referenced key value, <literal>InnoDB</literal> acts in foreign
- key checks as if the other parent rows with the same key value
- do not exist. For example, if you have defined a
- <literal>RESTRICT</literal> type constraint, and there is a
- child row with several parent rows, <literal>InnoDB</literal>
- does not allow the deletion of any of those parent rows.
- </para>
-
- <para>
- <literal>InnoDB</literal> performs cascading operations through
- a depth-first algorithm, based on records in the indexes
- corresponding to the foreign key constraints.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>: A
- <literal>FOREIGN KEY</literal> constraint that references a
- non-<literal>UNIQUE</literal> key is not standard SQL. It is an
- <literal>InnoDB</literal> extension to standard SQL.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- If <literal>ON UPDATE CASCADE</literal> or <literal>ON UPDATE
- SET NULL</literal> recurses to update the <emphasis>same
- table</emphasis> it has previously updated during the cascade,
- it acts like <literal>RESTRICT</literal>. This means that you
- cannot use self-referential <literal>ON UPDATE CASCADE</literal>
- or <literal>ON UPDATE SET NULL</literal> operations. This is to
- prevent infinite loops resulting from cascaded updates. A
- self-referential <literal>ON DELETE SET NULL</literal>, on the
- other hand, is possible, as is a self-referential <literal>ON
- DELETE CASCADE</literal>. Cascading operations may not be nested
- more than 15 levels deep.
- </para>
-
- <para>
- <emphasis role="bold">Deviation from SQL standards</emphasis>:
- Like MySQL in general, in an SQL statement that inserts,
- deletes, or updates many rows, <literal>InnoDB</literal> checks
- <literal>UNIQUE</literal> and <literal>FOREIGN KEY</literal>
- constraints row-by-row. According to the SQL standard, the
- default behavior should be deferred checking. That is,
- constraints are only checked after the <emphasis>entire SQL
- statement</emphasis> has been processed. Until
- <literal>InnoDB</literal> implements deferred constraint
- checking, some things will be impossible, such as deleting a
- record that refers to itself via a foreign key.
- </para>
-
- <para>
Here is a simple example that relates <literal>parent</literal>
and <literal>child</literal> tables through a single-column
foreign key:
@@ -3672,6 +3540,134 @@
1005 and refers to errno 150 in the error message.
</para>
+ <para>
+ If MySQL reports an error number 1005 from a
+ <literal role="stmt">CREATE TABLE</literal> statement, and the
+ error message refers to errno 150, table creation failed because
+ a foreign key constraint was not correctly formed. Similarly, if
+ an <literal role="stmt">ALTER TABLE</literal> fails and it
+ refers to errno 150, that means a foreign key definition would
+ be incorrectly formed for the altered table. You can use
+ <literal>SHOW ENGINE INNODB STATUS</literal> to display a
+ detailed explanation of the most recent
+ <literal>InnoDB</literal> foreign key error in the server.
+ </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>
+
+ <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 SIMPLE</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 is not well defined for
+ operations such as <literal role="stmt">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. For other storage engines, MySQL Server parses
+ and ignores 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
+ <literal>DB_ROW_ID</literal>, <literal>DB_TRX_ID</literal>,
+ <literal>DB_ROLL_PTR</literal> and
+ <literal>DB_MIX_ID</literal>). The server will report error 1005
+ and refers to <literal>errno</literal> -1 in the error message.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ If there are several rows in the parent table that have the same
+ referenced key value, <literal>InnoDB</literal> acts in foreign
+ key checks as if the other parent rows with the same key value
+ do not exist. For example, if you have defined a
+ <literal>RESTRICT</literal> type constraint, and there is a
+ child row with several parent rows, <literal>InnoDB</literal>
+ does not allow the deletion of any of those parent rows.
+ </para>
+
+ <para>
+ <literal>InnoDB</literal> performs cascading operations through
+ a depth-first algorithm, based on records in the indexes
+ corresponding to the foreign key constraints.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>: A
+ <literal>FOREIGN KEY</literal> constraint that references a
+ non-<literal>UNIQUE</literal> key is not standard SQL. It is an
+ <literal>InnoDB</literal> extension to standard SQL.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ If <literal>ON UPDATE CASCADE</literal> or <literal>ON UPDATE
+ SET NULL</literal> recurses to update the <emphasis>same
+ table</emphasis> it has previously updated during the cascade,
+ it acts like <literal>RESTRICT</literal>. This means that you
+ cannot use self-referential <literal>ON UPDATE CASCADE</literal>
+ or <literal>ON UPDATE SET NULL</literal> operations. This is to
+ prevent infinite loops resulting from cascaded updates. A
+ self-referential <literal>ON DELETE SET NULL</literal>, on the
+ other hand, is possible, as is a self-referential <literal>ON
+ DELETE CASCADE</literal>. Cascading operations may not be nested
+ more than 15 levels deep.
+ </para>
+
+ <para>
+ <emphasis role="bold">Deviation from SQL standards</emphasis>:
+ Like MySQL in general, in an SQL statement that inserts,
+ deletes, or updates many rows, <literal>InnoDB</literal> checks
+ <literal>UNIQUE</literal> and <literal>FOREIGN KEY</literal>
+ constraints row-by-row. According to the SQL standard, the
+ default behavior should be deferred checking. That is,
+ constraints are only checked after the <emphasis>entire SQL
+ statement</emphasis> has been processed. Until
+ <literal>InnoDB</literal> implements deferred constraint
+ checking, some things will be impossible, such as deleting a
+ record that refers to itself via a foreign key.
+ </para>
+
</section>
<section id="innodb-and-mysql-replication">
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r12292 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-6.0 | paul.dubois | 5 Nov |
