Oracle中Constraint的状态参数initially与deferrable

最新推荐文章于 2022-05-27 16:10:59 发布

rudy_gao

最新推荐文章于 2022-05-27 16:10:59 发布

阅读量1.4k

点赞数

分类专栏： oracle_basic

oracle_basic 专栏收录该内容

127 篇文章

订阅专栏

本文详细介绍了Oracle数据库中约束状态的两个关键参数：initially和deferrable，并通过具体实例展示了initially immediate、initially deferred、initially immediate deferrable及initially deferred deferrable等不同配置下对数据插入的影响。

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

在Oracle数据库中，关于约束的状态有下面两个参数：
            initially （initially immediate 或 initially deferred）
            deferrable（deferrable 或 not deferrable）
      第1个参数，指定默认情况下，约束的验证时刻（在事务每条子句结束时，还是在整个事务结束时）。
      第2个参数，指定了在事务中，是否可以改变上一条参数的设置。
      如果不指定上述参数，默认设置是 initially immediate not deferrable。
      注意：如果约束是not deferrable，那么它只能是initially immediate，而不能是initially deferred。

测试①，initially immediate：
SQL> create table nlist (
2 nid number
3 );

Table created

SQL> alter table nlist add constraint pk_nlist primary key (nid) initially immediate;

Table altered

SQL> insert into nlist values (1);

1 row inserted

SQL> insert into nlist values (1);

insert into nlist values (1)

ORA-00001: 违反唯一约束条件 (TEST.PK_NLIST)

测试②，initially deferred：
SQL> create table nlist (
2      nid number
3 );

Table created

SQL> alter table nlist add constraint pk_nlist primary key (nid) initially deferred;

Table altered

SQL> insert into nlist values (1);

1 row inserted

SQL> insert into nlist values (1);

1 row inserted

SQL> commit;

commit

ORA-02091: 事务处理已回退
ORA-00001: 违反唯一约束条件 (TEST.PK_NLIST)

测试③，initially immediate deferrable：
SQL> create table nlist (
2      nid number
3 );

Table created

SQL> alter table nlist add constraint pk_nlist primary key (nid) initially immediate deferrable;

Table altered

SQL> set constraint pk_nlist deferred;

Constraints set

SQL> insert into nlist values (1);

1 row inserted

SQL> insert into nlist values (1);

1 row inserted

SQL> commit;

commit

ORA-02091: 事务处理已回退
ORA-00001: 违反唯一约束条件 (TEST.PK_NLIST)

测试④，initially deferred deferrable：
SQL> create table nlist (
2      nid number
3 );

Table created

SQL> alter table nlist add constraint pk_nlist primary key (nid) initially deferred deferrable;

Table altered

SQL> set constraint pk_nlist immediate;

Constraints set

SQL> insert into nlist values (1);

1 row inserted

SQL> insert into nlist values (1);

insert into nlist values (1)

ORA-00001: 违反唯一约束条件 (TEST.PK_NLIST)

测试⑤：
SQL> create table nlist (
2 nid number
3 );

Table created

SQL> alter table nlist add constraint pk_nlist primary key (nid) initially deferred no deferrable;

alter table nlist add constraint pk_nlist primary key (nid) initially deferred no deferrable

ORA-01735: 无效的 ALTER TABLE 选项