Oracle中Constraint的状态参数initially与deferrable

在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 选项
转载自http://hi.baidu.com/linkzq/item/be23df424c9312e3bdf4511c

转载于:https://www.cnblogs.com/iImax/archive/2012/09/11/2680511.html