The column is not null

Question

In Oracle, pending constraints are checked only at the commit point.

What is the meaning of the DEFERRABLE clause in the case of a NOT NULL constraint? for example

create table test(a number not null deferrable, b number); insert into test(a,b) values (222, 111); commit; 

After these statements, I thought the following code would work

 update test set a = null where b = 111; delete test where b = 111; commit; 

But this is not so.

What is the difference between the two definitions?

 create table test1(a number not null deferrable, b number); create table test2(a number not null, b number); 

Answer 1

There are two options. Or you need to set a limit that should be deferred in the transaction using the command shown below

 SET CONSTRAINTS ALL DEFERRED; 

This must be done before executing the UPDATE that you defined.

Alternatively, you can set the INITIALLY DEFERRED constraint in the table definition

 create table test(a number not null initially deferred deferrable, b number); 

After completing any of these steps, you should be able to run the DML that you have in the question.