Initially immediate vs initially deferred

This is the default when neither initially immediate nor initially deferred has been specified. The constraint is checked after each SQL statement.

create table init_immediate (
  id   number primary key initially immediate,
  data varchar2(50)
);

Initially deferred

drop table init_immediate;

create table init_immediate (
  id   number       primary key initially deferred,
  data varchar2(50) not null initially deferred
);


insert into init_immediate values ( null, 'one');
insert into init_immediate values (    3, null);
insert into init_immediate values (    8, 'eight');
insert into init_immediate values (    3, 'two');

update init_immediate set id = 1 where data = 'one';
update init_immediate set id = 2 where data = 'two';
update init_immediate set data = 'three' where id = 3;

commit;

ORA-02447

A not deferrable constraint cannot be set to initially deferred, it raises an ORA-02447: cannot defer a constraint that is not deferrable which seems logical:

create table no_no_ora_02447 (
  id   number primary key not deferrable initially deferred,
  data varchar2(50)
);


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger