Re: user_constraint columns

DEFERRABLE Constraints
You can specify table and column constraints as DEFERRABLE or NOT DEFERRABLE. DEFERRABLE means that the constraint will not be checked until the transaction is committed. The default is NOT DEFERRABLE.

If you specify DEFERRABLE, you can also specify the constraint's initial state as INITIALLY DEFERRED and thereby start the transaction in DEFERRED mode. Or you can specify a DEFERRABLE constraint's initial state as INITIALLY IMMEDIATE and start the transaction in NOT DEFERRED mode.

Example I
The following statement creates table GAMES with a NOT DEFERRABLE INITIALLY IMMEDIATE constraint check on the SCORES column:

CREATE TABLE games (scores NUMBER CHECK (scores >= 0)); Example III
To define a unique constraint on a column as INITIALLY DEFERRED DEFERRABLE, issue the following statement:

CREATE TABLE orders
  (ord_num NUMBER CONSTRAINT unq_num UNIQUE (ord_num)    INITIALLY DEFERRED DEFERRABLE); A constraint cannot be defined as NOT DEFERRABLE INITIALLY DEFERRED.

Use SET CONSTRAINT(S) to set, for a single transaction, whether a deferrable constraint is checked following each DML statement or when the transaction is committed. You cannot alter a constraint's deferrability status; you must drop the constraint and re-create it.

See Oracle8 Administrator's Guide and Oracle8 Concepts for more information about deferred constraints.

Enabling and Disabling Constraints
Constraints can have one of three states: DISABLE, ENABLE NOVALIDATE, or ENABLE VALIDATE. Taking a constraint from a disabled to enable validated state requires an exclusive lock on the table, because while all old data is being checked for validity, no new data can be entered into the table. Due to this behavior, only one constraint can be enabled at a time, and each new constraint must check all existing rows by serial scan.

To avoid locking the table, place the constraint in the ENABLE NOVALIDATE state, using the ENABLE clause. This state ensures that all new DML statements on the table are validated, therefore Oracle does not need to prevent concurrent access to the table.

ENABLE NOVALIDATE also allows you to place several of the table's constraints in the ENABLE VALIDATE state concurrently. Each scan that Oracle performs to validate existing data can also be performed in parallel when possible.

Placing constraints concurrently in the ENABLE VALIDATE state requires that you issue multiple ALTER TABLE commands from separate sessions.

Paul in VT Received on Mon Aug 16 1999 - 12:18:00 CDT