Thanks, Kim! Some comments:
- SET CONSTRAINTS section: We should add a note after this sentence:
> When you use the statement to change a constraint from DEFERRED to
> IMMEDIATE, the constraint is checked > as soon as the statement is
E.g. "If the check fails, the transaction is not rolled back; an
error here constitutes a statement level error only, so one can use
this statement to check if all constraints are fulfilled before
> Derby must find the name of the corresponding index by inspecting
> the system tables, which can impede performance. attempting a
> commit. (A commit with remaining violations would roll back the
> transaction, see the section on commit.)"
Sorry I wasn't clear here: it is the user (application) that would
need to find the index name by performing queries against the system
tables. The main worry here isn't performance, probably, but that it's
awkward and requires extra (not portable) SQL.
> - A SET CONSTRAINTS statement is executed
This should be "A SET CONSTRAINTS statement which sets the constraint
mode to IMMEDIATE is executed".
> - A return from a stored procedure reverts the constraint mode to immediate
We ought perhaps to explain this item in a little more detail
somewhere. We could do it here and/or in a section on calls of stored
procedures, your call:
"If the constraint mode of a constraint is immediate before a call to
a store procedure, and the stored procedure sets the constraint mode
of that constraint to deferred, the constraint mode is implicitly
re-set to immediate on return from the stored procedure. This is
because the constraint mode is pushed on a stack when we enter the
stored procedure, cf other session state variables, like the current
role. If a constraint violation happens as a result of this, the
transaction is rolled back and an exception is thrown."
> any delete of a parent row can only lead to a violation (or deferred
> checking as the case may be) when the last of possibly several
> duplicates is deleted.
This reads a bit weird: Let me try a rephrasing:
"any delete of a parent row can lead to a foreign key violation
immediately (or at deferred checking time, if the foreign key is also
referred, as the case may be) when the last of possibly several
key duplicates of the referenced key is deleted or updated"
> When you change the constraint mode from deferred to immediate, the
> constraint is checked.
-> "When you change the constraint mode explicitly using SET
CONSTRAINTS, the constraint is checked, but slightly differently
that at commit time: if a violation ...."
It is probably best to move this paragraph after the next one: "If a violation is seen at commit time..."
We should probably look at the section that explains commit (if there is one!), and add the info (or a cross reference) on constraint checking there too. Likewise we should add info (or a cross reference) in a section on stored procedure calls