oracle 約束(constraint)的幾個引數的小研究

逍遙三人發表於2012-02-09
OracleAI
轉載：http://blog.csdn.net/kywinder/article/details/5282619

首先搞清下幾個概念：
ORACLE中，約束分deferred 跟 immediate 2種：
  deferred:如果 Oracle 在事務提交（commit）時才對約束執行檢查，則稱此約束是延遲的（deferred）。如果資料違反了延遲約束，提交操作將導致事務被回滾（undo）。 
  immediate:如果約束是即時的（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才行。

SQLPLUS(oracle 10.2.0.1):
--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) disabledand 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) disabledand 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) disabledand 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) disabledand 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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21584437/viewspace-715985/，如需轉載，請註明出處，否則將追究法律責任。

相關文章