Oracle约束状态

第一：

oracle约束的4种状态：

disable novalidate 既不会约束新增数据也不会验证已有数据，等同于disable

enable novalidate 约束新增数据但不会验证已有数据

disable validate 约束新增数据但不会验证已有数据，启用后禁止DML

enable validate 约束新增数据并验证已有数据，等同于enable

测试：

--1.创建一个实验表
CREATE TABLE check_test AS SELECT * FROM scott.emp
--2.查询
select * from check_test;
--3.增加约束
alter table check_test add constraint id_unique unique(empno);
--4.查看现在约束的装状态
select t.owner,t.constraint_name,t.status,t.deferrable,t.deferred,t.validated from user_constraints t where t.constraint_name=‘ID_UNIQUE‘
t.owner,t.constraint_name,t.status, t.deferrable, t.deferred, t.validated
1 TEST ID_UNIQUE ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
看到 status 与 validated分别是 ENABLE VALIDATED
--5.观察到状态后进行插入操作（这条数据位empno已经存在。）
insert into check_test values(7934,‘EVAN‘,‘CLERK‘,7782,to_date(‘1989/8/22‘,‘YYYY-MM-DD‘),6000,NULL,10);
结果：ORA-00001: 违反唯一约束条件 (TEST.ID_UNIQUE)
--6.修改状态为：
alter table check_test modify constraint id_unique disable novalidate;
--7.再次执行插入操作。
insert into check_test values(7934,‘EVAN‘,‘CLERK‘,7782,to_date(‘1989/8/22‘,‘YYYY-MM-DD‘),6000,NULL,10);
结果：1 row inserted
观察索引会发现之前的唯一索引被自动删除：
select * from user_indexES where index_name=‘ID_UNIQUE‘
--8.再次修改状态
alter table check_test modify constraint id_unique enable novalidate;
结果： ORA-02299: 无法验证 (TEST.ID_UNIQUE) - 找到重复关键字。
原因：因为enable会去创建唯一性索引，而已有数据deptno存在重复数据10，所以这里不能enable
--9.删除重复的。
delete from check_test where empno=7934 and ENAME=‘EVAN‘;
再次执行：alter table check_test modify constraint id_unique enable novalidate;
结果：Table altered；
--10.
select index_name,table_name,uniqueness from dba_indexes where index_name = ‘ID_UNIQUE‘;
再次插入：

insert into check_test values(7934,‘EVAN‘,‘CLERK‘,7782,to_date(‘1989/8/22‘,‘YYYY-MM-DD‘),6000,NULL,10);
结果：ORA-00001: 违反唯一约束条件 (TEST.ID_UNIQUE)
--11.再次改变状态
alter table check_test modify constraint id_unique disable validate;
再次插入数据：结果：ORA-25128: 不能对带有禁用和验证约束条件 (TEST.ID_UNIQUE) 的表进行插入/更新/删除

--12.新增一个约束
alter table check_test add constraint check_sal check(sal between 0 and 7000);
--12修改新约束check_sal状态
alter table check_test modify constraint check_sal disable novalidate;
插入数据
insert into check_test values(7776,‘EVAN‘,‘CLERK‘,7782,to_date(‘1989/8/22‘,‘YYYY-MM-DD‘),8000,NULL,10);
commit;
alter table check_test modify constraint check_sal enable novalidate;
insert into check_test values(7777,‘EVAN‘,‘CLERK‘,7782,to_date(‘1989/8/22‘,‘YYYY-MM-DD‘),8000,NULL,10);
结果：ORA-02290: 违反检查约束条件 (TEST.CHECK_SAL)
alter table check_test modify constraint check_sal enable validate;
结果：ORA-02293: 无法验证 (TEST.CHECK_SAL) - 违反检查约束条件

第二：

约束延迟验证相关内容：

Deferrable Constraints

Every constraint is either in a not deferrable (default) or deferrable state. This state determines when Oracle Database checks the constraint for validity. The following graphic depicts the options for deferrable constraints

If a constraint is not deferrable, then Oracle Database never defers the validity check of the constraint to the end of the transaction. Instead, the database checks the constraint at the end of each statement. If the constraint is violated, then the statement rolls back

You can set the default behavior for when the database checks the deferrable constraint. You can specify either of the following attributes:

• INITIALLY IMMEDIATE

  The database checks the constraint immediately after each statement executes. If the constraint is violated, then the database rolls back the statement.

• INITIALLY DEFERRED

  The database checks the constraint when a COMMIT is issued. If the constraint is violated, then the database rolls back the transaction

测试：

--1.
select t.owner,t.constraint_name,t.deferrable,t.deferred from user_constraints t where t.constraint_name=‘CHECK_SAL‘
结果：

OWNER CONSTRAINT_NAME DEFERRABLE DEFERRED
------------------------------------------------------------ ------------------------------ -------------- ---------
TEST CHECK_SAL NOT DEFERRABLE IMMEDIATE

此约束为不能延迟

--2.重建约束check_sal
alter table check_test drop constraint CHECK_SAL;
Table altered
alter table check_test add constraint CHECK_SAL check(sal between 1 and 7000) deferrable;
Table altered
--3.再次查询：
select t.owner,t.constraint_name,t.deferrable,t.deferred from user_constraints t where t.constraint_name=‘CHECK_SAL‘
结果：
OWNER CONSTRAINT_NAME DEFERRABLE DEFERRED
------------------------------------------------------------ ------------------------------ -------------- ---------
TEST CHECK_SAL DEFERRABLE IMMEDIATE
--4.执行插入
insert into check_test values(7776,‘EVAN‘,‘CLERK‘,7782,to_date(‘1989/8/22‘,‘YYYY-MM-DD‘),8000,NULL,10);
结果：ORA-02290: 违反检查约束条件 (TEST.CHECK_SAL)
--5.修改约束状态：
set constraint check_sal DEFERRED;
Constraints set
再次插入数据： insert into check_test values(7776,‘EVAN‘,‘CLERK‘,7782,to_date(‘1989/8/22‘,‘YYYY-MM-DD‘),8000,NULL,10);
结果：1 row inserted

提交：commit;
结果：
ORA-02091: 事务处理已回退
ORA-02290: 违反检查约束条件 (TEST.CHECK_SAL)