Below is the list of changes that have just been committed into a local
mysqldoc repository of jan. When jan does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.2644 05/05/10 08:51:17 jan@stripped +1 -0
Added more documentation of the referential action done in
INSERT, UPDATE, and DELETE in case of foreign key constraints
containing ON UPDATE and ON DELETE subclauses.
Docs/manual.texi
1.2832 05/05/10 08:51:11 jan@stripped +49 -22
Added more documentation of the referential action done in
INSERT, UPDATE, and DELETE in case of foreign key constraints
containing ON UPDATE and ON DELETE subclauses.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: jan
# Host: hundin.mysql.fi
# Root: /home/jan/mysqldoc
--- 1.2831/Docs/manual.texi Tue May 10 07:24:52 2005
+++ 1.2832/Docs/manual.texi Tue May 10 08:51:11 2005
@@ -68387,6 +68387,55 @@
@end itemize
+@code{InnoDB} rejects any @code{INSERT} or @code{UPDATE} operation
+that attempts to create a foreign key value in a child table without
+a matching candidate key value in the parent table. The action
+@code{InnoDB} takes for any @code{UPDATE} or @code{DELETE} operation
+that attempts to update or delete a candidate key value in the parent
+table that has some matching rows in the child table is dependent on
+the @emph{referential action} specified using @code{ON UPDATE} and
+@code{ON DETETE} subclauses of the @code{FOREIGN KEY} clause. When
+the user attempts to delete a row from a parnt table, and there
+are one or more matching rows in the child table, @code{InnoDB}
+supports five options regarding the action to be taken:
+
+@itemize @bullet
+
+@item @code{CASCADE}: Delete the row from the parent table and
+automatically delete the matching rows in the child table.
+@code{ON DELETE CASCADE} is available starting from MySQL 3.23.50
+and @code{ON UPDATE CASCADE} is available starting from 4.0.8.
+
+@item @code{SET NULL}: Delete the row from the parent table and
+set the foreign key column(s) in the child table to @code{NULL}.
+This is only valid if the foreign key columns do not have the
+@code{NOT NULL} qualifier specified. @code{ON DELETE CASCADE}
+is available starting from MySQL 3.23.50 and @code{ON UPDATE CASCADE}
+is available starting from 4.0.8.
+
+@item @code{SET DEFAULT}: @code{InnoDB} rejects table definitions
+containing @code{ON DELETE SET DEFAULT} clauses.
+
+@item @code{NO ACTION}: In @code{ANSI SQL-92} standard,
+@code{NO ACTION} means @emph{no action} in the sense that an attempt
+to delete or update a primary key value will not be allowed to
+proceed if there is a related foreign key value in the referenced
+table (Gruber, Mastering SQL, 2000:181). Starting from 4.0.18
+@code{InnoDB} rejects the delete operation from the parent
+table.
+
+@item @code{RESTRICT}: Rejects the delete operation from the
+parent table
+
+@end itemize
+
+@code{InnoDB} supports the same options when the candidate key in
+the parent table is update. With @code{CASCADE}, the foreign key
+column(s) in the child table are set to new value(s) of the
+candidate key in the parent table. In the same way, the updates
+cascade if updated column(s) in the child table reference
+foreign keys in another table.
+
@code{InnoDB} needs 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, these indexes are created automatically. In older versions,
@@ -68423,20 +68472,6 @@
is a child row with several parent rows, @code{InnoDB} does not allow
the deletion of any of those parent rows.
-Starting from MySQL 3.23.50, you can also associate the
-@code{ON DELETE CASCADE} or @code{ON DELETE SET NULL} clause with
-the foreign key constraint. Corresponding @code{ON UPDATE} options
-are available starting from 4.0.8. If @code{ON DELETE CASCADE} is
-specified, and a row in the parent table is deleted, @code{InnoDB}
-automatically deletes also all those rows in the child table
-whose foreign key values are equal to the referenced key value in
-the parent row. If @code{ON DELETE SET NULL} is specified, the
-child rows are automatically updated so that the columns in the
-foreign key are set to the SQL @code{NULL} value.
-@code{SET DEFAULT} is parsed but ignored by MySQL. @code{InnoDB}
-rejects table definitions containing @code{ON DELETE SET DEFAULT}
-clauses.
-
@code{InnoDB} performs cascading operations through a depth-first algorithm,
based on records in the indexes corresponding to the foreign key
constraints.
@@ -68460,14 +68495,6 @@
constraints row-by-row. According to the SQL standard, the default behavior
should be that constraints are only checked after the WHOLE SQL statement
has been processed.
-
-Since 4.0.18 if an attribute contains @code{ON DELETE NO ACTION}
-foreign key constraint @code{InnoDB} does not allow the deletion
-of any of those parent rows. In @code{ANSI SQL-92} standard,
-@code{NO ACTION} means @emph{no action} in the sense that an attempt
-to delete or update a primary key value will not be allowed to
-proceed if there is a related foreign key value in the referenced
-table (Gruber, 2000:181).
A simple example that relates @code{parent} and @code{child} tables through a
single-column foreign key:
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (jan:1.2644) | jan.lindstrom | 10 May |
