Oracle - Deferring Constraints.

Constraints can have the following attributes:

• DEFERRABLE or NOT DEFERRABLE
• INITIALLY DEFERRED or INITIALLY IMMEDIATE

You can defer checking constraints for validity until the end of the transaction. A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constraint is violated, then commit causes the transaction to roll back.

Usage scenario: Company policy dictates that department number 40 should be changed to 45. Changing the DEPARTMENT_ID column affects employees assigned to this department. Therefore, you make the primary key and foreign keys deferrable and initially deferred. You update both department and employee information, and at the time of commit, all rows are validated.

Example: 1

CREATE TABLE tmp_dep (
dep_id NUMBER,
dep_name VARCHAR2(50));

ALTER TABLE tmp_dep
ADD CONSTRAINT tmp_dep_pk PRIMARY KEY (dep_id);

INSERT INTO tmp_dep
VALUES(40, 'Finance Department');

INSERT INTO tmp_dep
VALUES(41, 'Production Department' );

CREATE TABLE tmp_emp (
emp_id NUMBER,
emp_name VARCHAR2(50),
dep_id NUMBER);

ALTER TABLE tmp_emp
ADD CONSTRAINT tmp_emp_pk PRIMARY KEY (emp_id);

ALTER TABLE tmp_emp
ADD CONSTRAINT tmp_emp_fk FOREIGN KEY(dep_id)
REFERENCES tmp_dep(dep_id);

INSERT INTO tmp_emp
VALUES(1, 'Jophn', 40);

INSERT INTO tmp_emp
VALUES(2, 'Peter', 40);

When we will try to change the existing department id 40 to 45 we get the following error message.

UPDATE tmp_dep
SET dep_id = 45
WHERE dep_name = 'Finance Department'

ORA-02292: integrity constraint (IFSAPP.TMP_EMP_FK) violated - child record found

In this case we can achieve this by make constrains as DEFERRABALE’.

--drop the constraints
ALTER TABLE tmp_emp
DROP CONSTRAINT tmp_emp_fk;

ALTER TABLE tmp_dep
DROP CONSTRAINT tmp_dep_pk;

-- add the constraints with DEFERRABLE

ALTER TABLE tmp_dep
ADD CONSTRAINT tmp_dep_pk PRIMARY KEY (dep_id)
DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE tmp_emp
ADD CONSTRAINT tmp_emp_fk FOREIGN KEY(dep_id)
REFERENCES tmp_dep(dep_id)
DEFERRABLE INITIALLY DEFERRED;

--Update the existing department id in the tmp_dep and tmp_emp tables.
UPDATE tmp_dep
SET dep_id = 45
WHERE dep_name = 'Finance Department';
1 row updated

UPDATE tmp_emp
SET dep_id=45
WHERE dep_id = 40;
2 rows updated

Difference Between INITIALLY DEFERRED and INITIALLY IMMEDIATE

A constraint that is defined as deferrable can be specified as either INITIALLY DEFERRED or INITIALLY IMMEDIATE. The INITIALLY IMMEDIATE clause is the default.

CREATE TABLE emp_new_sal (
salary NUMBER
CONSTRAINT sal_ck CHECK (salary > 100)
DEFERRABLE INITIALLY IMMEDIATE,
bonus NUMBER
CONSTRAINT bonus_ck CHECK (bonus > 0 )
DEFERRABLE INITIALLY DEFERRED );

• The sal_ck constraint is created as DEFERRABLE INITIALLY IMMEDIATE

• The bonus_ck constraint is created as DEFERRABLE INITIALLY DEFERRED

Example 1: Insert a row that violates sal_ck

INSERT INTO emp_new_sal VALUES(90,5);

ORA-02290: check constraint (IFSAPP.SAL_CK) violated

The sal_ck constraint defined as INITIALLY IMMEDIATE so the constraint verified immediately after the INSERT statement.

Example 2: Insert a row that violates bonus_ck

INSERT INTO emp_new_sal VALUES(110, -1);

1 row inserted

The bonus_ck constraint defined as INITIALLY DEFERRED so the row insertion is successful. Now commit the transaction.

COMMIT;

ORA-02091: transaction rolled back
ORA-02290: check constraint (IFSAPP.BONUS_CK) violated

The commit failed due to constraint violation. Therefore, at this point, the transaction is rolled back by the database.

Example 3: Set the DEFERRED status to all constraints

SET CONSTRAINTS ALL DEFERRED;

INSERT INTO emp_new_sal VALUES(90,5);

1 row inserted

COMMIT;

ORA-02091: transaction rolled back
ORA-02290: check constraint (IFSAPP.SAL_CK) violated

Now both constraints are set to DEFERRED status so sal_ck constraint is verified when the COMMIT happens.

Example4: Set the IMMEDIATE status to both the constraints

SET CONSTRAINT ALL IMMEDIATE;

INSERT INTO emp_new_sal VALUES(110, -1);

ORA-02290: check constraint (IFSAPP.BONUS_CK) violated

Now both constraints are set to IMMEDIATE status so bonus_ck constraint is verified immediate after the INSERT statement.

Note: If you create a table without specifying constraint deferability, then the constraint is checked immediately at the end of each statement.

CREATE TABLE newemp_details(
emp_id NUMBER,
emp_name VARCHAR2(20),
CONSTRAINT newemp_det_pk PRIMARY KEY(emp_id));

When you attempt to defer the newemp_det_pk constraint that is not deferrable, you observe the error message.

SET CONSTRAINT newemp_det_pk DEFERRED;

ORA-02447: cannot defer a constraint that is not deferrable