On 16 April 2015 at 21:39, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> You are, not unexpectedly, assuming that constraints are evaluated only
> after all rows has been processed - i.e., post-statement completion. While
> this is possible (see below) it is not the default behavior. By default, as
> each row is updated all of the relevant constraints are checked to see if
> any have been violated.
>
Thanks. It's interesting that the default behaviour is to compromise
ACID compliance with a result that is, logically speaking,
non-deterministic. This appears to be inconsistent with the ISO SQL
standard [1] and with other SQL DBMSs.
David
[1] I only have the SQL 1999 and 2003 documentation to hand. In both
cases Section 10 of the Foundation document specifies that immediate
constraint checking (whether deferrable or not) occurs "on completion
of any SQL-statement".