column not null deferrable

Question

In Oracle deferred constraints are checked only at the point of commit.

What is the meaning of DEFERRABLE clause in a case of 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 it doesn't.

What is the difference between two definitions?

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

Jenjena

Answer 1

There are two options here. Either you need to set the constraint to be deferred within the transaction by using the command shown below

SET CONSTRAINTS ALL DEFERRED;

This should be run before doing the UPDATE statement that you have defined.

Alternatively you can set the constraint to be INITIALLY DEFERRED in the table definition

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

After doing either of these things, you should then be able to run the DML that you have in the question.

Ramirez

Yes, you are right. With INITIALLY DEFERRED my code works. Thanks. – Jenjena