martijntonies2000 wrote:

>
> What do DEFERRED constraints actually mean? When DO they start
> checking? And when don't they?

Depends on the implementation.

I can think of 2 meaningful times to DEFER constraint evaluation until:

1. deferred until the completion of the statement, which means that
if the data becomes temporarily invalid during the execution of a single statement, but by the completion of the statement
it corrects itself then the constraint is satisfied.

i.e. lame example but demonstrative:
constraint: CHECK (A = (SELECT AVG(A) FROM THISTABLE))

attempted statement: UPDATE THISTABLE SET A = 5;

(where the table has more than 1 row and in both rows "A" currently = 7)

This statement would fail if the constraint was evaluated on each row, because when any single row is updated
the Average value of A would be somewhere between 5 and 7, and "A" in the that row would be 5.

If the constraint is deferred, then A will = 5 in all rows after execution and the statement will succeed.


2. deferred until COMMIT time. Probably a more powerful constraint, but potentially confusing, as the constraint is really only a constraint on
the Initial state of a transaction and not actually a constraint on any changes made during the transaction, furthermore, a transaction
may go on for a very long time, only to need to be rolled back because it violates some deferred constraint.


One benefit of deferred constraints is possible performance improvements, and another benefit is the flexibility to move from 1 legal state to another legal state
but pass through illegal states temporarily.

Possible draw backs are that it would be very easy to create deadlock situations, especially with deffer until commit constraints. And neither transaction would
have any idea it was destined to be rolled back, because a change another user commited in the database, causes a prior action to require a constraint violation
to commit.

Anyway, that is a deferred constraint. They do not exist in Interbase, and I have not used them on any platform which supports them.

I have no first hand experience with them.

Cheers

David Z