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 选项

上一篇:阿里联合ICA联盟发布IoT系列标准,智能门锁标准首批授权测试机构名单出炉


下一篇:监控应用服务