Tip :- How do I set the constraint as Deferrable ?

By default the constraint is non deferrable .i.e oracle will notify immediately when there is a constraint violation.

By setting the constraint as deferrable , oracle checks violation only at commit point at the end of the transaction.

Test case :-

ora816 SamSQL :> create table emp (empno number,ename varchar2(10), constraint emp_pk primary key (empno));

ora816 SamSQL :> insert into emp values(1,'sam');

ora816 SamSQL :> insert into emp values(1,'wad');

(Error returned by oracle)ORA-00001: unique constraint (SYS.EMP_PK) violated

so you are not able to insert duplicate column because of primary key.

Now let us try to make the constraint as deferred

ora816 SamSQL :> set constraint emp_pk deferred;

(Error returned by oracle)ORA-02447: cannot defer a constraint that is not deferrable

This error indicate that the primary key created above is not deferrable , so we have to make the primary key as deferrable as

ora816 SamSQL :> create table emp (empno number,ename varchar2(10), constraint emp_pk primary key (empno) deferrable);

ora816 SamSQL :> insert into emp values(1,'wad');

ora816 SamSQL :> insert into emp values(1,'sam');

(Error returned by oracle)ORA-00001: unique constraint (SYS.EMP_PK) violated

Since we made the primary key as deferrable while creation, we can set the constraint as deferred.

ora816 SamSQL :> set constraint emp_pk deferred;

Constraint set.

ora816 SamSQL :> insert into emp values(1,'wad');

ora816 SamSQL :> insert into emp values(1,'sam');

ora816 SamSQL :> insert into emp values(1,'Oracle');

ora816 SamSQL :> insert into emp values(2,'DBA');

ora816 SamSQL :> commit;

(Error returned by oracle)ORA-02091: transaction rolled back ORA-00001: unique constraint (SYS.EMP_PK) violated

You noticed with above case that oracle shows the constraints violation only as commit time at the end of transaction.

Note : You can also check constraints before oracle complain as

SQL :> set constraint all immediate

so do this before commit, otherwise your entire transaction will rollback in case of violation.

Conclusion : Deferrable is very useful clause because it checks the constraint integrity before commit only. It gives flexibility to developer to write a program with out worrying about constraint violation until transaction ends.

End of Tip	Welcome to send comments or feedback at Wadhwa_s@Hotmail.com
	Copyright © Oracle Techniques All Rights Reserved