Re: when are check() constraints evaluated

The rules in SQL-92 are that constriants can be deferred during a transaction, if they were declared deferable. Let me quote it for you:

4.10.1 Checking of constraints

Every constraint is either deferrable or non-deferrable. Within a transaction, every constraint has a constraint mode; if a constraint is non-deferrable, then its constraint mode is always immediate, otherwise it is either or immediate or deferred. Every constraint has an initial constraint mode that specifies the constraint mode for that constraint at the start of each SQLtransaction  and immediately after definition of that constraint. If a constraint is deferrable, then its constraint mode may be changed (from immediate to deferred, or from deferred to immediate) by execution of a <set constraints mode statement>.

The checking of a constraint depends on its constraint mode within the current SQL-transaction. If the constraint mode is immediate, then the constraint is effectively checked at the end of each SQL-statement. If the constraint mode is deferred, then theconstraint is effectively checked when the constraint mode is changed to immediate either explicitly by execution of a <set constraints mode statement>, or implicitly at the end of the current SQL-transaction.

When a constraint is checked other than at the end of an SQL-transaction, if it is not satisfied, then an exception condition is raised and the SQL-statement that caused the constraint to be checked has no effect other than entering the exception information into the diagnostics area. When a <commit statement> is executed, all constraints are effectively checked and, if any constraint is not satisfied, then an exception condition is raised and the transaction is terminated by an implicit <rollback statement>. Received on Wed Dec 24 2003 - 01:56:15 CET