Initially immediate vs initially deferred

最新推荐文章于 2021-02-23 13:55:47 发布

iteye_7183

最新推荐文章于 2021-02-23 13:55:47 发布

阅读量282

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： SQL Oracle

本文链接：https://blog.youkuaiyun.com/iteye_7183/article/details/81681996

Oracle 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍了Oracle SQL中不同类型的约束，包括initially immediate和initially deferred的使用方式及区别，并通过实例展示了如何设置和使用deferrable约束。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[size=large][color=blue]Initially immediate[/color][/size]

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)
);

[size=large][color=blue]Initially deferred[/color][/size]

The constraint is checked when a transaction ends.
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;

It will be OK.

[size=large][color=blue]ORA-02447[/color][/size]

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

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

[size=large][color=blue]Deferrable constraint[/color][/size]

create table cons_deferrable_pk_tab (
  a number,
  b varchar2(10),
  c number not null deferrable,
  d date,
  primary key (a, b) deferrable
);

create table cons_deferrable_fk_tab (
  z,
  y,
  x varchar2(10),
  foreign key (z,y) references cons_deferrable_pk_tab deferrable
);

Now we insert data to the table will fails, because reference table has not been inserted data yet.

insert into cons_deferrable_fk_tab values (1, 'one', 'foo');

[b]ORA-02291: 整合性制約(ZOLO.SYS_C005382)に違反しました - 親キーがありません[/b]

set constraints all deferred

And now the constraints are deferred, so we can insert data which still not inserted to the reference table.

insert into cons_deferrable_fk_tab values (1, 'one', 'foo');

Though commit will fail.

[size=large][color=blue]set constraints [Oracle SQL][/color][/size]

set constraint[s] all { immediate | deferred };
set constraint[s] constraint-name-1 [, constraint-name-n ...] { immediate | deferred };