延迟约束

延迟约束：指仅当事物被提交时强制执行约束，在添加约束时可以使用deferrable字句来指定约束为延迟约束，对于已经存在的约束不能修改

case：

SQL> create table t(id number(4) notnull,name varchar2(20));

Table created.

---建立延迟约束

SQL> alter table t add constraintpk_t_id primary key(id) deferrable initially deferred;

Table altered.

 

SQL> insert into t values(1,'a');

1 row created.

 

SQL> insert into t values(1,'b');

1 row created.

 

SQL> commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-00001: unique constraint(SCOTT.PK_T_ID) violated

 

改为实时约束

SQL> set constraint pk_t_id immediate;

SQL> insert into t values(1,'a');

 

1 row created.

 

SQL> insert into t values(1,'b');

insert into t values(1,'b')

*

ERROR at line 1:

ORA-00001: unique constraint(SCOTT.PK_T_ID) violated