Hi,
I have been reading the constraints section in the reference manual on
FKs (http://db.apache.org/derby/docs/10.10/ref/rrefsqlj13590.html), and
was left puzzled. I have annotated the section with my comments on what
I think may be wrong below. I'd appreciate another set of eyes on this.
If my observations are correct, we should change the docs.
>
> Referential actions
>
> You can specify an ON DELETE clause and/or an ON UPDATE clause,
> followed by the appropriate action (CASCADE, RESTRICT, SET NULL, or NO
> ACTION) when defining foreign keys. These clauses specify whether
> Derby should modify corresponding foreign key values or disallow the
> operation, to keep foreign key relationships intact when a primary key
> value is updated or deleted from a table.
>
> You specify the update and delete rule of a referential constraint
> when you define the referential constraint.
>
> The update rule applies when a row of either the parent or dependent
> table is updated. The choices are NO ACTION and RESTRICT.
>
> When a value in a column of the parent table's primary key is updated
> and the update rule has been specified as RESTRICT, Derby checks
> dependent tables for foreign key constraints. If any row in a
> dependent table violates a foreign key constraint, the transaction is
> rolled back.
It seems that only the statement is rolled back even with RESTRICT.
> If the update rule is NO ACTION, Derby checks the dependent tables for
> foreign key constraints after all updates have been executed but
> before triggers have been executed. If any row in a dependent table
> violates a foreign key constraint, the statement is rejected.
"after all updates have been executed but before triggers have been
executed": wrong, it seems the reality is as follows: "after all
updates and BEFORE triggers have been executed, but before AFTER
triggers have been executed"
> When a value in a column of the dependent table is updated, and that
> value is part of a foreign key, NO ACTION is the implicit update
> rule. NO ACTION means that if a foreign key is updated with a non-null
> value, the update value must match a value in the parent table's
> primary key when the update statement is completed. If the update does
> not match a value in the parent table's primary key, the statement is
> rejected.
>
> The delete rule applies when a row of the parent table is deleted and
> that row has dependents in the dependent table of the referential
> constraint. If rows of the dependent table are deleted, the delete
> operation on the parent table is said to be propagated to the
> dependent table. If the dependent table is also a parent table, the
> action specified applies, in turn, to its dependents.
"If rows of the dependent table are deleted": I think this should
read "if rows in the dependent table are deleted as part of a CASCADE
on the parent table, the delete operation on the parent table is said
to be propagated to the dependent table."
> The choices are NO ACTION, RESTRICT, CASCADE, or SET NULL. SET NULL
> can be specified only if some column of the foreign key allows null
> values.
>
> If the delete rule is:
>
> NO ACTION, Derby checks the dependent tables for foreign key
> constraints after all deletes have been executed but before triggers
> have been executed. If any row in a dependent table violates a foreign
> key constraint, the statement is rejected.
>
> RESTRICT, Derby checks dependent tables for foreign key
> constraints. If any row in a dependent table violates a foreign key
> constraint, the transaction is rolled back.
No, it seems only the statement is rolled back.
> CASCADE, the delete operation is propagated to the dependent table
> (and that table's dependents, if applicable).
>
> SET NULL, each nullable column of the dependent table's foreign key is
> set to null. (Again, if the dependent table also has dependent tables,
> nullable columns in those tables' foreign keys are also set to null.)
What does the parenthesized sentence mean? There is no ON UPDATE SET
NULL.... and a mere ON DELETE SET NULL gives an error: the
intermediate table's row isn't deleted after all, it's FK is just set
to NULL...
> Each referential constraint in which a table is a parent has its own
> delete rule; all applicable delete rules are used to determine the
> result of a delete operation. Thus, a row cannot be deleted if it has
> dependents in a referential constraint with a delete rule of RESTRICT
> or NO ACTION.
Not true?: In the NO ACTION case, a BEFORE trigger could cause the
dependent row to be deleted, thus reestablishing harmony..
> Similarly, a row cannot be deleted if the deletion cascades to any of
> its descendants that are dependents in a referential constraint with
> the delete rule of RESTRICT or NO ACTION.
Same proviso as above
> Deleting a row from the parent table involves other tables. Any table
> involved in a delete operation on the parent table is said to be
> delete-connected to the parent table. The delete can affect rows of
> these tables in the following ways:
>
> If the delete rule is RESTRICT or NO ACTION, a dependent table is
> involved in the operation but is not affected by the
> operation. (That is, Derby checks the values within the table, but
> does not delete any values.) If the delete rule is SET NULL, a
> dependent table's rows can be updated when a row of the parent
> table is the object of a delete or propagated delete operation.
> If the delete rule is CASCADE, a dependent table's rows can be
> deleted when a parent table is the object of a delete. If the
> dependent table is also a parent table, the actions described in
> this list apply, in turn, to its dependents.
