SQL in a Nutshell, 3rd Edition

Name

SET CONSTRAINT Statement

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

Platform	Command
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 CONSTRAINT defines a value for the constraint mode of all deferrable constraints of the current transaction. If the session is not currently in a transaction, 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 IMMEDIATE;

The next example sets two constraints to defer ...