SQL in a Nutshell, 2nd Edition

Name

SET CONSTRAINT Statement —

Synopsis

The SET CONSTRAINT statement defines, for the current transaction, whether a deferrable constraint checks after each DML statement or when the transaction is finally committed. If the session is not currently in an open transaction, the setting applies to the next transaction.

Platform	Command
DB2	Not supported
MySQL	Not supported
Oracle	Supported, with variations
PostgreSQL	Supported, with variations
SQL Server	Not supported

SQL2003 Syntax

SET CONSTRAINT {constraint_name [,...] | ALL} {DEFERRED | IMMEDIATE}

Keywords

{constraint_name [,...] | ALL}

Names one or more deferrable constraints where the setting will apply. The keyword ALL sets the constraint mode for all deferrable constraints of the current transaction.

DEFERRED

Checks the conditions specified by the deferrable constraint when the transaction is committed, rather than when the DML statements are issued.

IMMEDIATE

Checks the conditions specified by the deferrable constraint immediately after each DML statement is issued, rather than when the transaction is committed.

Rules at a Glance

SET CONSTRAIN defines a value for the constraint mode of all deferrable constraints of the current transaction. If the session is not currently in a transaction, then the SET CONSTRAINT statement applies to the next transaction issued during the session.

The following example sets all deferrable constraints to be checked immediately following the issuance of each DML statement:

SET CONSTRAINT ALL ...