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
1TESTID_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)

  

Oracle約束狀態