约束的4种状态以及immediate、deferred

--整理以前的学习笔记

1、约束的4种状态：
enable validate/disable novalidate/enable novalidate/disable validate

create table dept(deptno number(2) primary key,dname varchar2(20) not null);

alter table dept add constraint dept_deptno_ck check (deptno>20);--新创建的约束默认状态是enable validate

alter table dept modify constraint dept_deptno_ck disable  validate;--这种情况不能对表执行DML操作

SQL> insert into dept values(12,'HR');
insert into dept values(12,'HR')
ORA-25128: 不能对带有禁用和验证约束条件 (U1.DEPT_DEPTNO_CK) 的表进行插入/更新/删除
SQL> insert into dept values(21,'HR');
insert into dept values(21,'HR')
ORA-25128: 不能对带有禁用和验证约束条件 (U1.DEPT_DEPTNO_CK) 的表进行插入/更新/删除

alter table dept modify constraint dept_deptno_ck disable novalidate;
SQL> insert into dept values(12,'HR');
1 row inserted
SQL> commit;
Commit complete

SQL> alter table dept modify constraint dept_deptno_ck enable validate;
alter table dept modify constraint dept_deptno_ck enable validate
ORA-02293: 无法验证 (U1.DEPT_DEPTNO_CK) - 违反检查约束条件
--因为表中有不符合U1.DEPT_DEPTNO_CK的数据，所以无法验证，必须更改数据后进行验证

alter table dept modify constraint dept_deptno_ck enable novalidate;

SQL> insert into dept values(12,'HR');
insert into dept values(12,'HR')
ORA-02290: 违反检查约束条件 (U1.DEPT_DEPTNO_CK)

update dept set deptno=21 where deptno=12;
commit;

SQL> alter table dept modify constraint dept_deptno_ck enable validate;
Table altered
--此时验证通过

2、约束的immediate和deferred
immediate:在执行语句时检查约束
deferred:提交事务时检查约束
在上例中，在插入不合法的数据时，可以不将约束修改为disable novalidate,可以设置约束为deferred

user01用户下：
create table dept(deptno number(2) primary key,dname varchar2(20) not null);
alter table dept add constraint dept_deptno_ck check (deptno>20);
SQL> insert into dept values(12,'HR');
insert into dept values(12,'HR')
ORA-02290: 违反检查约束条件 (USER01.DEPT_DEPTNO_CK)

user01用户下：
SQL> select constraint_name,deferrable,deferred from user_constraints where

constraint_name='DEPT_DEPTNO_CK';
 
CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
DEPT_DEPTNO_CK                 NOT DEFERRABLE IMMEDIATE

sys用户下：
SQL> select constraint_name,deferrable,deferred from dba_constraints where

constraint_name='DEPT_DEPTNO_CK';
 
CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
DEPT_DEPTNO_CK                 NOT DEFERRABLE IMMEDIATE
SQL>alter table dept modify constraint dept_deptno_ck deferrable initially immediate;--没有这种语法，书上说

了，无法在后来使约束成为延迟约束，必须在创建约束的时候使约束称为延迟约束（deferrable）
alter table dept modify constraint dept_deptno_ck deferrable initially immediate
ORA-00933: SQL 命令未正确结束
SQL> alter table dept drop constraint dept_deptno_ck;
Table altered
SQL> alter table dept add constraint dept_deptno_ck check (deptno>20) deferrable initially immediate;
Table altered

user01用户下：
SQL> select constraint_name,deferrable,deferred from user_constraints where

constraint_name='DEPT_DEPTNO_CK';
 
CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
DEPT_DEPTNO_CK                 DEFERRABLE     IMMEDIATE

sys用户下：
SQL> select constraint_name,deferrable,deferred from dba_constraints where

constraint_name='DEPT_DEPTNO_CK';
 
CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
DEPT_DEPTNO_CK                 DEFERRABLE     IMMEDIATE
SQL> select * from dept;
 
DEPTNO DNAME
------ --------------------
 
SQL> set constraint dept_deptno_ck deferred;
 
Constraints set
 
SQL> insert into dept values(12,'HR');--在语句执行的时候不检查
 
1 row inserted
 
SQL> commit;--在提交时检查
 
commit
 
ORA-02091: 事务处理已回退
ORA-02290: 违反检查约束条件 (USER01.DEPT_DEPTNO_CK)

SQL> set constraint dept_deptno_ck deferred;--这条语句执行以后可以认为是开启了一个事务，因为在PL/SQL中表现

为“提交”按钮变亮了，如果set constraint之后提交，那么下面的insert还是没有延迟的，也就是说，commit之后

deferred解除
 
Constraints set
 
SQL> insert into dept values(12,'HR');
 
1 row inserted
 
SQL> update dept set deptno=21 where deptno=12;
 
1 row updated
 
SQL> commit;
 
Commit complete
SQL> set constraint dept_deptno_ck deferred;
 
Constraints set
 
SQL> commit;
 
Commit complete
 
SQL> insert into dept values(12,'HR');
 
insert into dept values(12,'HR')
 
ORA-02290: 违反检查约束条件 (USER01.DEPT_DEPTNO_CK)

不过，奇怪的是，无论有没有set constraint dept_deptno_ck deferred;查询user_constraints表中DEFERRED都是

immediate

注意：在enabled/disabled/validate/novalidate之间更改约束状态的操作会影响到所有会话，此状态是一个数据字典更

新，在immediate和deferred之间切换可延迟约束是会话特有的，但初始状态应用于所有会话
下面演示初始状态是deferred的情况（deferrable initially deferred）

SQL> delete from dept;
 
1 row deleted
 
SQL> commit;
 
Commit complete
 
SQL> alter table dept drop constraint dept_deptno_ck;
 
Table altered
 
SQL> alter table dept add constraint dept_deptno_ck check (deptno>20) deferrable initially deferred;
 
Table altered
 
SQL> select constraint_name,deferrable,deferred from user_constraints where

constraint_name='DEPT_DEPTNO_CK';
 
CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
DEPT_DEPTNO_CK                 DEFERRABLE     DEFERRED
 
SQL> insert into dept values(12,'HR');
 
1 row inserted
 
SQL> commit;
 
commit
 
ORA-02091: 事务处理已回退
ORA-02290: 违反检查约束条件 (USER01.DEPT_DEPTNO_CK)
 
SQL> set constraint dept_deptno_ck immediate;
 
Constraints set
 
SQL> insert into dept values(12,'HR');
 
insert into dept values(12,'HR')
 
ORA-02290: 违反检查约束条件 (USER01.DEPT_DEPTNO_CK)

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26524307/viewspace-1061267/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26524307/viewspace-1061267/