ORACLE中,約束分deferred 跟 immediate 2種:
deferred:如果 Oracle 在事務提交(commit)時才對約束執行檢查,則稱此約束是延遲的(deferred)。如果數據違反了延遲約束,提交操作將導致事務被回滾(undo)。
immediate:如果約束是即時的(immediate)(非延遲的),則此約束將在語句執行結束后進行檢查。如果數據違反了延遲約束,語句將被立即回滾。
解釋一下,即:將約束檢驗延遲到了執行COMMIT的時候。以下是具體的解釋和舉例。 定義為可延遲(deferrable)的約束可以指定為:
1. initially immediate(初始化立即執行)或
2. initially deferred(初始化延遲執行)。
解釋二者的區別。初始化立即執行/延遲執行規定了在默認情況下應該如何執行約束:
初始化立即執行--在每條語句執行結束時檢驗約束
初始化延遲執行--一直等到事務完成后(或者調用set constraint immediate語句時)才檢驗約束
一般情況下,我們用的約束初始都是immediate型的(默認),而且不好轉為deferred型。但是如果初始是deferrable(需要手動指定),那deferred跟immediate 2種狀態可以隨意轉換。
此外,約束有以下4種狀態:
ENABLE(啟用)確保所有輸入的數據都遵從約束(constraint)
DISABLE(禁用)總是允許輸入數據,無論數據是否遵從約束
VALIDATE(驗證)確保已存在的數據遵從約束
NOVALIDATE(無驗證)允許已存在的數據不遵從約束
ENABLE VALIDATE 與 ENABLE 相同。Oracle 將檢查約束,並保證所有數據均遵從約束。
ENABLE NOVALIDATE 表示所有新插入或被修改的數據都必須遵從約束,但允許已存在的數據不遵從約束。
DISABLE NOVALIDATE 與 DISABLE 相同。Oracle 不會檢查約束.
DISABLE VALIDATE 將禁用約束,移除約束使用的索引,並禁止修改約束鍵的數據。
約束不論哪種類型,要能夠生效,必須狀態是enable才行。
--deferred 跟 immediate的對比試驗-----------------------------
SQL> drop table aa purge;
Table dropped.
SQL> create table aa ( id number,name varchar2(20),constraint pk primary key(id));
Table created.
SQL> col constraint_name for a11
SQL> select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME ,STATUS,DEFERRABLE,DEFERRED,validated from u
ser_constraints where table_name='AA';
CONSTRAINT_ C TABLE STATUS DEFERRABLE DEFERRED VALIDATED
----------- - ----- -------- -------------- --------- -------------
PK P AA ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
--可以看到,默認的是NOT DEFERRABLE,下面我們將它轉為immediate試試
SQL> set constraint pk immediate;
set constraint pk immediate
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable
--增加一個uk,指定deferrable initially immidiate|deferred
SQL> alter table aa add constraint uk unique (name) deferrable initially immediate;
Table altered.
SQL> select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME ,STATUS,DEFERRABLE,DEFERRED,validated from u
ser_constraints where table_name='AA';
CONSTRAINT_ C TABLE STATUS DEFERRABLE DEFERRED VALIDATED
----------- - ----- -------- -------------- --------- -------------
PK P AA ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
UK U AA ENABLED DEFERRABLE IMMEDIATE VALIDATED
SQL> select * from aa;
no rows selected
SQL> insert into aa values(1,'SDF');
1 row created.
SQL> insert into aa values(2,'SDF');
insert into aa values(2,'SDF')
*
ERROR at line 1:
ORA-00001: unique constraint (LYN.UK) violated
SQL> set constraints uk deferred;
Constraint set.
SQL> select * from aa;
no rows selected
SQL> insert into aa values(1,'SDF');
1 row created.
SQL> insert into aa values(2,'SDF');
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (LYN.UK) violated
此外,可以在SESSION里設定所有約束是立即檢查(immediate)還是延遲檢查(deferred),當然,這只影響初始時指定可延遲的(deferrable)約束。
SQL> alter session set constraint=immediate;
Session altered.
SQL> alter session set constraints=deferred;
Session altered.
--4種狀態對比-----------------
SQL> drop table aa purge;
Table dropped.
SQL> create table aa(id number primary key);
Table created.
--enable validate(默認)
SQL> select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME ,STATUS,DEFERRABLE,DEFERRED,validated from
user_constraints where table_name='AA';
CONSTRAINT_ C TABLE STATUS DEFERRABLE DEFERRED VALIDATED
----------- - ----- -------- -------------- --------- -------------
SYS_C002829 P AA ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
SQL> insert into aa select 2010 from dual;
1 row created.
SQL> insert into aa select 2010 from dual;
insert into aa select 2010 from dual
*
ERROR at line 1:
ORA-00001: unique constraint (LYN.SYS_C002829) violated
--disable novalidate
SQL> alter table aa modify constraints SYS_C002829 disable;
Table altered.
SQL> select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME ,STATUS,DEFERRABLE,DEFERRED,validated from
user_constraints where table_name='AA';
CONSTRAINT_ C TABLE STATUS DEFERRABLE DEFERRED VALIDATED
----------- - ----- -------- -------------- --------- -------------
SYS_C002829 P AA DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED
SQL> select * from aa;
ID
----------
2010
SQL> insert into aa select 2010 from dual;
1 row created.
SQL> insert into aa select 2010 from dual;
1 row created.
SQL> commit;
Commit complete.
--disable validate
SQL> alter table aa modify constraints SYS_C002829 disable validate;
alter table aa modify constraints SYS_C002829 disable validate
*
ERROR at line 1:
ORA-02437: cannot validate (LYN.SYS_C002829) - primary key violated
SQL> select * from aa;
ID
----------
2010
2010
2010
SQL> delete aa where rownum<3;
2 rows deleted.
SQL> select rownum,id from aa;
ROWNUM ID
---------- ----------
1 2010
SQL> alter table aa modify constraints SYS_C002829 disable validate;
Table altered.
SQL> update aa set id=2012;
update aa set id=2012
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabled and validated
SQL> insert into aa select 2012 from dual;
insert into aa select 2012 from dual
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabled and validated
SQL> delete from aa;
delete from aa
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabled and validated
SQL> truncate table aa;
truncate table aa
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabled and validated
--enable novalidate
SQL> alter table aa enable novalidate constraint SYS_C002829;
Table altered.
SQL> insert into aa select 2010 from dual;
insert into aa select 2010 from dual
*
ERROR at line 1:
ORA-00001: unique constraint (LYN.SYS_C002829) violated