oracle constraints immediate,Oracle 約束(constraint)的幾個參數的小研究

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