DBA Tips Archive for Oracle


Deferred Integrity Constraints in Oracle8

by Jeff Hunter, Sr. Database Administrator


Up until the release of Oracle8, all integrity constraints in the database where considered immediate constraints. With an immediate constraint, if a user enters data that violates a constraint, the statement is rolled back and Oracle signals an error. With the introduction of Oracle8, users now have the option of selecting between immediate and a new option called deferred itegrity constraints.
Deferred Integrity Constraints
Deferred integrity constraints are those that are not enforced until the user attempts to commit the transaction. 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.
Why use deferrable integrity constraints?
  • One use is pure laziness. In Oracle7, users had no way of entering data into a child table before entering the matching data into the parent table. With Oracle8, users now 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.
  • Another feature is speed. Anyone that has ever dealt with inserting large amounts of data into a table, like found in a data warehousing environment, know of the cost loading (inserting) data into tables. 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.
  • Solving design issues. Take for example the two tables ORDER and ORDER LINE. In this model, we cannot have an ORDER LINE unless we have an ORDER, but we also assert that all ORDERS must have ORDER LINES. This catch-22 issue can easily be resolved by deferring all integrity constraints until the time of the commit.
  • Warnings
    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 commit time errors.
  • 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.
  • Are all constraints deferrable?
    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 constraint is created with the not deferrable option (the default), then user processes will always be bound by immediate integrity constraint.
    Default behavior of constraints
    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. 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.

    NOTE: The initially deferred and not deferrable keyword are mutually exclusive.

    New columns in the CONSTRAINTS view
    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:

          , deferrable
          , deferred
        FROM user_constraints
        WHERE constraint_name like 'EMP%';
        CONSTRAINT_NAME                 DEFERRABLE      DEFERRED
        ------------------------------  --------------  ---------
        EMP_SELF_KEY                    NOT DEFERRABLE  IMMEDIATE
        EMP_FOREIGN_KEY                 DEFERRABLE      DEFERRED
       REM -- ---------------------
       REM -- Create example tables
       REM -- ---------------------
       DROP TABLE emp;
       DROP TABLE dept;
       CREATE TABLE dept (
           deptno             NUMBER(2) NOT NULL
         , dname              CHAR(14)
         , loc                CHAR(13)
         , CONSTRAINT dept_pk PRIMARY KEY (deptno)
       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)
       INSERT INTO emp VALUES (1001, 'JEFF', 'PRESIDENT', 10);
       INSERT INTO emp VALUES (1002, 'MELODY', 'MANAGER', 30);
       INSERT INTO emp VALUES (1003, 'MARK', 'MANAGER', 10);
       INSERT INTO emp VALUES (1004, 'MARTIN', 'MANAGER', 20);
       REM -- -------------------------------
       REM -- Try to delete from parent table
       REM -- -------------------------------
       DELETE FROM dept WHERE deptno = 10;
       ERROR at line 1:
       ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated - child record found
       REM -- --------------------------------------------------------
       REM -- Set this transaction to defer all deferrable constraints
       REM -- --------------------------------------------------------
       DELETE FROM dept WHERE deptno = 10;
       1 row deleted.
       ERROR at line 1:
       ORA-02091: transaction rolled back
       ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated - child record found

    Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

    All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

    I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

    Last modified on
    Thursday, 28-Aug-2008 14:18:36 EDT
    Page Count: 31878