oracle約束學習（1）unique和check

    有人說，沒有索引, 拿什麼來保證約束?姑且不論這話的對錯，但約束的實現（除了not null），很多都是通過索引來快速定位約束的地方。unique約束會自動建立索引，pk也是。也因此，約束的很多問題總是和索引纏綿一起。

 

    相關視圖：

              dba_constraints
              dba_cons_columns

 

    not null約束比較特別點。not null的索引類型和check約束一樣，都是C。只有not null這種約束才會被繼承，記住這一點，在複製表時，別忘了加約束、索引等。

 

    unique  約束
    延遲驗證屬性：deferrable/not deferrable, Deferred/immediate  

    延遲約束在提交的時候才進行校正，大多數是這樣的。

    not deferrable：不提交也檢查，這是預設值。

SQL> select * from hr.t1;        ID NAME---------- ----------------------------------------         1 d         2 bSQL> conn hr/hr已串連。SQL> alter table t1 add constraint t1_u_1 unique(id); -- 預設情況下是not deferrable & immediate表已更改。SQL> insert into t1 values(1,'c');insert into t1 values(1,'c')*第 1 行出現錯誤:ORA-00001: 違反唯一約束條件 (HR.T1_U_1)SQL> alter table t1 drop constraint t1_u_1 cascade;表已更改。SQL> alter table t1 add constraint t1_u_2 unique(id) deferrable; --deferrable & immediate表已更改。SQL> select * from t1;        ID NAME---------- ----------------------------------------         1 d         2 bSQL> insert into t1 values(1,'c');insert into t1 values(1,'c')*第 1 行出現錯誤:ORA-00001: 違反唯一約束條件 (HR.T1_U_2)

    為什麼延遲還還報錯呢？延遲性約束，索引必須非唯一。enable novalidate也必須是非唯一索引。然而，我們在建unique約束的時候，oracle會隱士的先建立一個唯一性索引，而且索引的名字和約束的名字是一樣的。再來建立約束，除非這個列本來有一個約束。

SQL> alter table t1 add constraint t1_u_6 unique(id);表已更改。SQL> select index_name,uniqueness from user_indexes ;INDEX_NAME                                                   UNIQUENESS------------------------------------------------------------ ------------------T1_U_6                                                       UNIQUE

   

   

SQL> alter table t1 add constraint t1_u_3 unique(id) deferrable; --deferrable & immediate表已更改。SQL> select * from t1;        ID NAME---------- ----------------------------------------         1 d         2 bSQL> insert into t1 values(1,'c');insert into t1 values(1,'c')*第 1 行出現錯誤:ORA-00001: 違反唯一約束條件 (HR.T1_U_3)SQL> alter table t1 drop constraint t1_u_3 cascade;表已更改。SQL> drop index t1_index;索引已刪除。SQL> create unique index t1_index on t1(id);索引已建立。SQL> select index_name,uniqueness from user_indexes;INDEX_NAME                                                   UNIQUENESS------------------------------------------------------------ ------------------T1_INDEX                                                     UNIQUESQL> alter table t1 add constraint t1_u_4 unique(id) deferrable;alter table t1 add constraint t1_u_4 unique(id) deferrable*第 1 行出現錯誤:ORA-01408: 此列列表已索引SQL> drop index t1_index;索引已刪除。SQL> alter table t1 drop constraint t1_u_4 cascade;alter table t1 drop constraint t1_u_4 cascade                               *第 1 行出現錯誤:ORA-02443: 無法刪除約束條件 - 不存在的約束條件SQL> alter table t1 add constraint t1_u_5 unique(id) initially deferred deferrable; --若是not deferrable，則只能跟immediate表已更改。SQL> select * from t1;        ID NAME---------- ----------------------------------------         1 d         2 bSQL> insert into t1 values(1,'c');已建立 1 行。SQL> insert into t1 values(1,'d');已建立 1 行。SQL> commit;commit*第 1 行出現錯誤:ORA-02091: 交易處理已回退ORA-00001: 違反唯一約束條件 (HR.T1_U_5)

    immediate deferrable是立馬延遲，而deferred deferrable則是在commit時給復原掉。

    check

    validate會檢查舊值，而novalidate不會。這個運用可以用來避免前台小姐誤操作。

   

 

SQL> select * from t1;        ID NAME---------- ----------------------------------------         1 d         2 b         3 c         4 cSQL> alter table t1 add constraint t1_u_6 check(id>10) validate;alter table t1 add constraint t1_u_6 check(id>10) validate                              *第 1 行出現錯誤:ORA-02293: 無法驗證 (HR.T1_U_6) - 違反檢查約束條件SQL> alter table t1 add constraint t1_u_6 check(id>10)  novalidate;表已更改。SQL> select * from t1;        ID NAME---------- ----------------------------------------         1 d         2 b         3 c         4 cSQL> insert into t1 values(5,'d');insert into t1 values(5,'d')*第 1 行出現錯誤:ORA-02290: 違反檢查約束條件 (HR.T1_U_6)SQL> alter table t1 add constraint t1_u_6 check(id>10) enable novalidate;表已更改。SQL> insert into t1 values(6,'d');insert into t1 values(6,'d')*第 1 行出現錯誤:ORA-02290: 違反檢查約束條件 (HR.T1_U_6)SQL> select * from t1;        ID NAME---------- ----------------------------------------         1 d         2 b         3 c         4 cSQL> alter table t1 add constraint t1_u_6 check(id>10) disable novalidate;表已更改。SQL> select * from t1;        ID NAME---------- ----------------------------------------         1 d         2 b         3 c         4 cSQL> insert into t1 values(5,'s');已建立 1 行。SQL> commit;提交完成。

    約束的優先順序比約束的屬性來得高。

SQL> create table t1 (id number,constraint t1_u_6 check(id>3) validate);表已建立。SQL> insert into t1 values(1);insert into t1 values(1)*第 1 行出現錯誤:ORA-02290: 違反檢查約束條件 (HR.T1_U_6)SQL> insert into t1 values(5);已建立 1 行。SQL> drop table t1 purge;表已刪除。SQL> create table t1(id number,constraint t1_u_6 check(id>3) novalidate);表已建立。SQL> insert into t1 values(1);insert into t1 values(1)*第 1 行出現錯誤:ORA-02290: 違反檢查約束條件 (HR.T1_U_6)SQL> insert into t1 values(5);已建立 1 行。SQL> select * from t1;        ID----------         5SQL> alter table t1 add constraint t1_u_6 check(id>10) novalidate;表已更改。SQL> insert into t1 values(3);insert into t1 values(3)*第 1 行出現錯誤:ORA-02290: 違反檢查約束條件 (HR.T1_U_6)SQL> insert into t1 values(7);insert into t1 values(7)*第 1 行出現錯誤:ORA-02290: 違反檢查約束條件 (HR.T1_U_6)SQL> insert into t1 values(11);已建立 1 行。SQL> select * from t1;        ID----------         5        11