Subject RE: [Firebird-Architect] Re: [IB-Architect] Rebuilding foreign keys system indexes
Author Claudio Valderrama C.
Ann Harrison wrote:
> In fact, that is the way constraints are supposed to work. The
> SQL standard considers UPDATE T1 SET A = 5 to be a single statement,
> not on statement for each instance of T1.

But I'm not sure the engine agrees. :-)
Let's see, this is a single statement. An exception would undo it
completely. There's a difference between 'NOW' and CURRENT_TIMESTAMP. 'NOW'
changes with each record: it's evaluated again. CURRENT_TIMESTAMP must not
change, it must be the same for each affected record, because this the
result of one single statement... and it doesn't change in IB/FB (SQL
compliance). But beyond this, PK/FK constraints can see any committed data
to enforce its presence, independent of the isolation level used by the
originator of the changes. However, the so-called CHECK-constraints are
triggers, triggers written behind scenes by the DSQL layer, triggers
affected by the isolation level of the current transaction, triggers that
will fire for each changed record, hence David Zvekic's example fails on
IB/FB as he predicted. Furthermore, David's check has to be put AFTER data
has been entered, or you won't be able to enter the first record: AVG
returns NULL the first time, therefore
fails even if you try to enter NULL (please let's not go again arguing that
NULL=NULL should be true instead of unknown or false).