DEFERRABLE INITIALLY DEFERRED

 

The general rule about DEFERRABLE constraints :

[NOT DEFERRABLE] is default, and means that every time a database modification statement is executed, the constraint is checked immediately afterwards, if the modification could violate the constraint.

However, if we declare a constraint to be DEFERRABLE, then we have the option of having it wait until a transaction is complete before checking the constraint.

We follow the keyword DEFERRABLE by either INITIALLY DEFERRED or INITIALLY IMMEDIATE. In the former case, checking will be deferred to just before each transaction commits. In the latter case, the check will be made immediately after each statement.

This is how default [NOT DEFERRABLE] works: If the table was created with invalid_city_ck_nn constraint, but this constraint wasn’t DEFERRABLE, then NULL data couldn’t be inserted from the very beginning. If the table was created without invalid_city_ck_nn constraint, and NULL data was inserted later into city column, and then you try to add invalid_city_ck_nn – it won’t be created at all. You have to go and correct all NULL values first in this case.

Now let’s examine how DEFERRABLE INITIALLY DEFERRED works

create table myTable ( city VARCHAR2(20 ) ) ; 
insert into myTable values ('New-York'); 

ALTER table myTable 
modify city constraint invalid_city_ck_nn NOT NULL 
DEFERRABLE INITIALLY DEFERRED; 

First of all our table was created. Then, one raw with NON-NULL data was inserted. And then, the city column has been modified, so NOT NULL constraint was enabled on it.
But this constraint was made DEFERRABLE with INITIALLY DEFERRED option. That means constraint is OFF at this time.
If so, we can try to insert the NULL data and see whether or not it will go through.

DEFERRABLE is associated with constraint. INITIALLY DEFERRABLE means this constraint is off initially. By having such a constraint we can easily insert NULL data into the city column.

insert into mytable values (''); 
insert into mytable values (NULL); 
select * from myTable;

As we can see NULL data was inserted. Means constraint was really DEFERRED.

Now, try to commit a transaction;

commit; 

and you will get this error

commit 
Error report - 
ORA-02091: transaction rolled back 
ORA-02290: check constraint (OE.INVALID_CITY_CK_NN) violated 
02091. 00000 - "transaction rolled back" 
*Cause: Also see error 2092. 
If the transaction is aborted at a remote site 
then you will only see 2091; 
if aborted at host then you will see 2092 and 2091. 
*Action: Add rollback segment and retry the transaction.

The transaction was rolled back, and we lost data inserted with last two insert statements.

select * from myTable;

will show us only one record, which is “New-York”, other two NULL values wasn’t inserted. This is how DEFERRABLE INITIALLY DEFERRED works –
it defers constraint violation check till COMMIT;

 

Leave a comment

Your email address will not be published. Required fields are marked *