Deferred Integrity Constraints

At the time of the commit, if any data entered during the transaction violates an integrity constraint, Oracle will then rollback the entire transaction and signal an error to the user.

Deferred integrity constraints should only be used when absolutely necessary. If at all possible, avoid hard-coding constraint names with your application code.

Deferring integrity constraints should only be used when solving a design issue. Be aware that very few applications support commits time errors.

Not all constraints in the database can be set to deferrable. Only when the constraint is created with the "deferrable" option can user processes set the constraint as deferrable.

If the integrity constraint is created with the "not deferrable" option, then user processes will always be bound by immediate integrity constraint. Along with defining whether a constraint is deferrable or not, you can also set its default behavior with the initially deferred and initially immediate statement.

For example, we can define the constraints as:

CREATE TABLE emp (
empno NUMBER(4) NOT NULL
, ename CHAR(10)
, job CHAR(9)
, deptno NUMBER(2) NOT NULL
, CONSTRAINT emp_fk1 FOREIGN KEY (deptno) REFERENCES dept (deptno)
INITIALLY IMMEDIATE
, CONSTRAINT emp_pk PRIMARY KEY (empno));

SET CONSTRAINT emp_no_fk_par DEFERRED;

Constraint set.

UPDATE parent
SET id = 2 WHERE id = 1;

1 row updated.

A constraint may be defined as deferrable, which means you can switch it between deferred and immediate.

If a constraint is in the deferred state, you can modify data in a way that fails the constraint test.

However, a constraint can only be deferred for the duration of a transaction. As soon as the transaction commits, the constraint becomes immediate.

You also have the option of explicitly setting the constraint back to immediate before the end of the transaction. At this point, your modifications will be tested against the constraint, and you will not be allowed to commit a transaction that leaves the data in an illegal state.

The initially deferred option will enable the constraint as deferrable by default whereas the initially immediate option requires the user process to explicitly set the transaction or session to deferrable.

The initially deferred and not deferrable keywords are mutually exclusivE.

Why use deferred integrity constraints?

• In Oracle, users have no way of entering data into a child table before entering the matching data into the parent table directly. However, users can have the option of inserting data into a child table before insert its matching data into the parent table by simply deferring the foreign-key constraint.
• Deferring the integrity checking until after the data has loaded can save considerable time when dealing with large volumes of data being inserted into tables.
• Many design issues can easily be resolved by deferring all integrity constraints until the time of the commit.

Creating deferred integrity constraints:

• To defer ALL constraints that are deferrable during the entire session, you can use the ALTER SESSION SET constraints=DEFERRED statement.
• A user can defer a named or all constraints that are deferrable for a specific transaction using the set constraint name deferred or set constraint all deferred.
• The user process may also set constraints for immediate enforcement for a specific transaction using the set constraint name immediate or set constraint all immediate.

Apart from that, there are new columns in the "user_constraints /dba_constraints /all_constraints" views that will give you information on whether the constraint is deferrable. For example:

SELECT
constraint_name
, deferrable
, deferred
FROM user_constraints
WHERE constraint_name like 'EMP%';

Return from Deferred integrity constraints to Oracle Database constraints