How to Use Deferrable Constraints in Oracle

Constraints are normally checked at the end of each statement. Some changes are only consistent as a whole: two rows that reference each other, a parent and child inserted in either order, or a swap of two unique values. A deferrable constraint can be checked at commit instead, so the transaction may pass through inconsistent states as long as it ends consistent.

Code for This Guide

The main examples are in the examples/tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

constraint name ... [not] deferrable [initially {immediate | deferred}]

set constraint[s] {name [, ...] | all} {immediate | deferred}

Rows That Reference Each Other

Two crew members are each other's partner. Whichever row is inserted first references a row that does not exist yet, so the foreign key must wait until commit.

Example:

create table crew_pairs (
  employee_id number primary key,
  partner_id  number constraint crew_pairs_partner_fk references crew_pairs
                     deferrable initially deferred
);

-- each row references the other: possible only when the check waits until commit
insert into crew_pairs values (112, 116);
insert into crew_pairs values (116, 112);
commit;

select * from crew_pairs order by employee_id;

Output:

Table CREW_PAIRS created.

1 row inserted.

1 row inserted.

Commit complete.

   EMPLOYEE_ID    PARTNER_ID
______________ _____________
           112           116
           116           112

When the Check Fails at Commit

A deferred violation is reported by COMMIT, and the whole transaction is rolled back.

Example:

set constraints crew_pairs2_fk deferred;
insert into crew_pairs2 values (120, 999);   -- 999 does not exist, but the check waits
commit;                                         -- the deferred check fails here
select count(*) as rows_kept from crew_pairs2;

Output:

Constraints CREW_PAIRS2_FK succeeded.

1 row inserted.

Error starting at line : 3
In command -
commit
Error report -
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (NIMBUS.CREW_PAIRS2_FK) violated - parent key
not found

   ROWS_KEPT
____________
           0

The constraint was declared INITIALLY IMMEDIATE and deferred for this transaction with SET CONSTRAINTS. The insert of a partner that does not exist succeeded, but COMMIT raised ORA-02091 and ORA-02291 and rolled the transaction back, so no row was kept.

Things to Know

• Run SET CONSTRAINTS ALL IMMEDIATE before COMMIT to check deferred constraints and get the error while the transaction is still open.
• Whether a constraint is deferrable is fixed when it is created; to change it, drop and re-create the constraint.
• A primary or unique key that is deferrable is enforced with a non-unique index.

Related Guides

• How to Create Identity Columns in Oracle

Conclusion

A deferrable constraint can be checked at commit instead of after each statement, either by default with INITIALLY DEFERRED or per transaction with SET CONSTRAINTS. Use it for changes that are only consistent as a whole, and expect violations to be reported, and the transaction rolled back, at commit.