The SET CONSTRAINTS statement sets the deferrability of one or more constraints.
The SET CONSTRAINTS statement allows you to set the constraint mode for one or more constraints either to DEFERRED or to IMMEDIATE.
When you use the statement to change a constraint from DEFERRED to IMMEDIATE, the constraint is checked as soon as the statement is executed.
If the check fails, the transaction is not rolled back; an error here constitutes a statement level error only. Therefore, you can use this statement to check if all constraints are fulfilled before you attempt to commit the transaction.
A SET CONSTRAINTS statement changes the state of a constraint only until the transaction ends (or until another, overriding SET CONSTRAINTS statement is issued). Once the transaction ends, the constraint reverts to the default behavior declared for it at the time it was created (using a CREATE TABLE or ALTER TABLE statement).
For more information on deferrable constraints, see CONSTRAINT clause and constraintCharacteristics.
It is recommended that you use SET CONSTRAINTS on table-level constraints. If you use SET CONSTRAINTS on a column-level constraint, you will need to find the name of the corresponding index by performing queries against the system tables, which is cumbersome and requires additional non-portable SQL.
SET CONSTRAINTS constraintNameList { DEFERRED | IMMEDIATE }
The constraintNameList is defined as follows:
ALL | constraintName [ { , constraintName }... ]
If the constraint mode is DEFERRED and a violation is seen at commit time, an exception is thrown, and the transaction is rolled back.
When you change the constraint mode explicitly to IMMEDIATE using SET CONSTRAINTS, the constraint is checked, but slightly differently from the way it is checked at commit time: if a violation is found, a statement-level exception is thrown. You can use this behavior to verify that constraints are fulfilled before you attempt to commit.
If the constraint mode is IMMEDIATE upon entering a stored routine, and that routine in a nested connection changes the constraint mode to DEFERRED, any constraints that are affected are checked upon return from the routine. If the check fails, an exception is thrown, and the transaction is rolled back.
Constraints with a constraint mode of DEFERRED are also checked if the application calls XAResource.prepare(Xid). If there is a violation, Derby throws XAException.XA_RBINTEGRITY, and the XA transaction is rolled back.
SET CONSTRAINTS FOO DEFERRED; SET CONSTRAINTS ALL DEFERRED; SET CONSTRAINTS FOO, BAR IMMEDIATE;