602SQL Documentation Index  

When do the Integrity Constraint Checks Take Place?

Server may check integrity constraints and referential integrity either when a statement is complete or when a transaction is complete. Check postponed on transaction's end is necessary, if the integrity validity is broken and then restored during a transaction. Check done immediately after a statement is useful, because the server doesn't have to maintain a list of all postponed checks.

An exception to this are modifications in a superior table in a referential integrity. The following action or an error is invoked immediately after such change.

Another exception are integrity constraints UNIQUE, which are checked immediately after each modification to the table.

The time for integrity constraints checks is set in this way:

evaluation ::= INITIALLY { IMMEDIATE | DEFERRED }

INITIALLY IMMEDIATE sets the check to be done immediately after the statement. INITIALLY DEFERRED postpones the internal integrity constraint check or the referential integrity check in the inferior table until the transaction is complete.

The evaluation time cannot be explicitly set for column property NOT NULL - however if it's desired, use the integrity constraint CHECK (column IS NOT NULL) instead of the NOT NULL property, for which the evaluation time can be set.

If no check time is specified, the default behaviour is chosen according to the SQLOPT_CONSTRS_DEFERRED (16384) compatibility attribute. This is valid even for the NOT NULL column property.