Foreign key semantics as documented

["Dag H. Wanvik" <da...@oracle.com>]

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.

Re: Foreign key semantics as documented

[Bryan Pendleton <bp...@gmail.com>]

> On 3/24/14 10:24 AM, Dag H. Wanvik wrote:
>> appreciate another set of eyes on this. If my observations are correct, we should change the docs.

Hi Dag, thanks for looking into this!

If this is not time-critical, this would be an
interesting set of sub-projects to take on as
part of DERBY-6466.

thanks,

bryan

Re: Foreign key semantics as documented

[Rick Hillegas <ri...@oracle.com>]

On 3/24/14 4:06 PM, Dag H. Wanvik wrote:
>
> On 24. mars 2014 19:52, Rick Hillegas wrote:
>> Hi Dag,
>>
>> I think that the docs should be adjusted to reflect what you have 
>> actually observed. In particular, the violation of a foreign key 
>> (regardless of the referential action) should at worst roll back the 
>> statement, not the transaction.
>>
> Thanks, Rick. I'll file a doc issue for this. Another item: the 
> standard defines what it means for a referencing set of columns to 
> match a referenced row with three forms of match: SIMPLE (default), 
> FULL and PARTIAL. The Derby doc doesn't really explain how we do the 
> matching in Derby, cf. this wording:
>
> "When you insert into or update a table with an enabled foreign key 
> constraint, Derby checks that the row does not violate the foreign key 
> constraint by looking up the corresponding referenced key in the 
> referenced table. If the constraint is not satisfied(*), Derby rejects 
> the insert or update with a statement exception."
>
> (*) not defined (my comment!)
>
> This doesn't address the matching performed when it comes to NULLs:
>
> For example, Derby accepts the following:
>
> create table parent(i int , j int, constraint c1 unique(i,j));
> create table child(i int, j int, constraint c2 foreign key(i,j) 
> references parent(i,j));
> insert into parent values (1,10);
> insert into child values (2,null);
>
> i.e. if there is a NULL in the referencing key, even the "2" is 
> accepted even if there is no row in the parent table containing i==2. 
> This corresponds to SQL standard SIMPLE matching (the default), which 
> is good, I guess, since we do not offer the non-default FULL or 
> PARTIAL matching methods. But it *would* be good to document how this 
> works, IMHO. At least this behavior wasn't obvious to me...
>
> Comments?
+1 to explaining the meaning of "matching".

Thanks,
-Rick
>
>
>
>

Re: Foreign key semantics as documented

["Dag H. Wanvik" <da...@oracle.com>]

On 24. mars 2014 19:52, Rick Hillegas wrote:
> Hi Dag,
>
> I think that the docs should be adjusted to reflect what you have 
> actually observed. In particular, the violation of a foreign key 
> (regardless of the referential action) should at worst roll back the 
> statement, not the transaction.
>
Thanks, Rick. I'll file a doc issue for this. Another item: the standard 
defines what it means for a referencing set of columns to match a 
referenced row with three forms of match: SIMPLE (default), FULL and 
PARTIAL. The Derby doc doesn't really explain how we do the matching in 
Derby, cf. this wording:

"When you insert into or update a table with an enabled foreign key 
constraint, Derby checks that the row does not violate the foreign key 
constraint by looking up the corresponding referenced key in the 
referenced table. If the constraint is not satisfied(*), Derby rejects 
the insert or update with a statement exception."

(*) not defined (my comment!)

This doesn't address the matching performed when it comes to NULLs:

For example, Derby accepts the following:

create table parent(i int , j int, constraint c1 unique(i,j));
create table child(i int, j int, constraint c2 foreign key(i,j) 
references parent(i,j));
insert into parent values (1,10);
insert into child values (2,null);

i.e. if there is a NULL in the referencing key, even the "2" is accepted 
even if there is no row in the parent table containing i==2. This 
corresponds to SQL standard SIMPLE matching (the default), which is 
good, I guess, since we do not offer the non-default FULL or PARTIAL 
matching methods. But it *would* be good to document how this works, 
IMHO. At least this behavior wasn't obvious to me...

Comments?

Re: Foreign key semantics as documented

[Rick Hillegas <ri...@oracle.com>]

On 3/24/14 10:24 AM, Dag H. Wanvik wrote:
> 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.
>
>
Hi Dag,

I think that the docs should be adjusted to reflect what you have 
actually observed. In particular, the violation of a foreign key 
(regardless of the referential action) should at worst roll back the 
statement, not the transaction.

Thanks,
-Rick