Ann W. Harrison wrote:

>>
>> which confuses me a bit. As the DELETE is a single transaction, shouldn't this be possible?
>>
>> Is my assumption (that this should be working) wrong?
>
> Err, well the assumption that it should work because the deletes are
> all in the same transaction is wrong. Firebird doesn't do deferred
> constraints (yet). I think it should work because all the deletes are
> in the same statement and (in theory) the constraint should be checked
> at the end of the statement, not row-by-row. However, most everything
> in Firebird is row-by-row, so this is consistent, if not correct.

Back in the early dark ages when we first looked at deferred
constraints it seemed like a huge performance problem, because
you'd have to keep track of every record change and validate
them all at the end of the transaction. Some of them would
still be in cache, but others would have been forced out. (In
the dark ages we didn't have gigs of RAM to spend on cache.)
It turns out that for foreign key constraints, you can validate
the constraint at the time the row is changed. If there's no
problem, you don't need to check again later, so you don't need
to keep a history of all changes, just those that failed validation.
They need to be checked again at the end of statement or transaction
because subsequent changes may have corrected the problem.

Just a thought. One of my few.


Cheers,

Ann