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?
