Oracle for Beginner: Deferrable Constraint

In this article I describe a simple example of deferrable constraint. With this feature, we can make the validation of the constraint process deferred until a commit is done. Sometime we need this condition when we try to load data from external file to our table.

SQL> create table testing(nilai number(5) check ( nilai in (10, 20, 30) ) deferrable initially deferred );
SQL> alter session set constraint = deferred;
SQL> insert into testing values(100);
(there are no errors! why?)
SQL> insert into testing values(20);
SQL> commit;
(there is an error, because there is data 100 that is not invalid for our check constraint)
SQL> select * from testing;
SQL> alter session set constraint = immediate;
SQL> insert into testing values (100);
(immediately, oracle will check the new data with check constraint)
SQL> insert into testing values (10);
SQL> commit;

There is another ways to manage a constraint. We can change the status of constraint. There are for kind of status: enable (validate), enable novalidate, disable validate, disable (novalidate). In this moment, we will try to change status of our constraint in table TESTING:

SQL> desc user_constraints;
SQL> select constraint_name from user_constraints where table_name = 'TESTING';
(with this query, you can obtain the constraint name for TESTING table. Please note that constraint_name)
SQL> alter table testing modify constraint const_name disable;
SQL> insert into testing values(50);
(there are no problems, because constraint is disabled)
SQL> insert into testing values(30);
SQL> commit;
SQL> alter table testing modify constraint const_name enable novalidate;
(what happen? why? )
SQL> insert into testing values (50);
(why oracle give you an error?)
SQL> insert into testing values(20);
SQL> commit;

That's all for today. Thanks.