Oracle 10G中關于限制在表和列中使用詳解

A:Check限制

1 --建立表

 2 create table order_status

 3 (

 4   id integer constraint order_status_pk primary key ,

 5   status varchar(120) ,

 6   last_modified date default sysdate

 7 );

 8 --添加Check限制

 9 alter table order_status 

10 add constraint order_status_ck check (status in('BeiJing','ShangHai','TaiWan'));

11 

12 alter table order_status

13 add constraint order_status_id_ck check(id>10);

14 --測試資料

15 insert into order_status(id,status)

16 values(132,'ShangHai'); 

注意:

(1)在建立Check限制時,表中所有的行必須滿足添加限制條件, 如果在添加限制已經存在資料 條件不滿足會提示一個ORA-02293錯誤:截圖如下:

<a target="_blank" href="http://blog.51cto.com/attachment/201201/112706327.gif"></a>

一般處理在添加限制前最好清空原表中資料,上面提示錯誤有一個例外:在添加限制将其禁用. 這就涉及到"啟用限制"和"禁用限制"操作.後面詳解.還可以指定Enable Novalidate選項.進而使限制隻适用于新添加資料.而不必管限制添加前資料.在插入資料時如果資料不符Check限制會提示一個ORA-02290錯誤.修改插入資料即可.

B:Not Null限制

--添加上述建立表 not null限制

 alter table order_status

 modify status constraint  order_status_newref not null;

 alert table order_status

 modify last_modified not null;

 格式:注意使用Modify 而非以前使用的Add Constraint

 格式如下:

 alert table [表名] modify [表中列名] constraint [限制唯一标示] not null; 

A1:在為Last_modified添加限制時 并沒有使用限制命名 是允許的 系統會自動配置設定一個名稱來辨別. 但建議使用手動使用一個有意義的命名 出現錯誤容易判斷出處

C:Foreign Key外鍵限制.

1 --建立一個外鍵限制測試表

 2 create global temporary table test_orderstatus

 4   id integer constraint order_statustest_pk primary key,

 5   status_id number(15),

 6   status varchar(120),

 7   last_modifieddate date default sysdate

 8 );

 9 

10 --添加一列 先删除 後添加

11 alter table test_orderstatus

12 drop  column status_id;

13 

14 alter table test_orderstatus

15 add constraint order_status_modify_fk status_id reference order_status(id);

對Foreign key 使用On Delete Cascade子句.即指定當父表中删除一行記錄時 回自動删除子表中于其外鍵關聯的行記錄.

1 --附帶自動級聯更新

2 alter table test_orderstatus

3 drop  column status_id;

4 

5 alter table test_orderstatus

6 add constraint order_status_modify_fk status_id reference order_status(id) on delete cascade; 

當在删除了父表一條記錄時可以不删除子表記錄. 全部設定字表中于外鍵關聯所有記錄為Null 則使用On Delete Cascade Null子句. 同樣加在 外鍵限制後位置.當删除一條父表記錄時.字表所有通過目前外鍵同父表關聯的資料都設定為空值.

D:Unique限制

1 --添加Unique限制

2 alter table order_status

3 add constraint order_status_uq unique(status); 

E:删除限制(Delete References)

1 --删除剛剛設定的Unique限制

3 drop constraint order_status_uq;【限制唯一辨別】 

當需要手動編碼删除限制時. 找到限制唯一命名是關鍵. 是以推薦建立使用有意義的限制命名.容易識别

F:禁用限制(Disable References)

--在建立時就禁用限制

alter table order_status

add constraint order_status_uq unique(status) disable;

添加限制預設是在建立後自動啟用的. 建立時可以直接禁用 直接尾部加一個Disable子句 如果在建立後 在來禁用限制則使用Alter table .....Disable Constraint子句

1 --使用中禁用限制

2 alert table order_status

3 disable constraint order_status_uq;【禁用上面建立唯一限制】 

E:啟用限制(ReStart References)

1 --啟用已經建立限制

3 enable constraint order_status_uq;

限制預設在建立後自動啟用.要啟用限制注意目前表中所有資料必須滿足限制條件. 不然會包ORA-02293錯誤.當然有時為了需要可以隻對新插入資料進行限制.原始依然存在,使用Alter Table.......Enable Novalidate. 預設是Enable Constraint.

--隻對新插入的資料才執行目前限制

alert table order_status

enable novalidate constraint order_status_uq; 

shangmian我們在用到上面第一個Check限制時也談到這個問題.當我們建立Check限制,因為表中在建立限制前原來還存在資料 其中這些資料有些不符合目前限制的 但是我們不想删掉. 那麼使用Enable Novalidate子句就是一個很好選擇, 它子對新插入的資料其限制. 原始資料進行保留.

G:延遲限制(Deferred Constraint)

延遲限制是在事務被送出時強制執行的限制.添加限制時可以通過Deferrable子句來指定限制為延遲限制. 限制一但建立以後, 就不能修改為Deferrable延遲限制.唯一辦法: 删除該限制,隻能在建立指定為延遲限制即可.

1 --目前來指定上述建立的唯一限制為延遲限制 先删除已經建立唯一限制 

2 --在建立中再次指定為延遲

3 alter table order_status

4 drop constraint order_status_uq

5 

6 alert table order_status

7 add constraint order_status_uq unique (status) deferrable initially deferred; 

注意上面再添加限制時. 可以将其辨別為Initially Immediate 或Initially Deferred.

其中Initially Immediate意思是每次向表中添加資料,修改資料或是從表中删除資料時.都要檢查這個限制.(這與限制預設行為相同). 而Initially Deferred.意思是隻有事務被送出時才檢查這個限制. 上述我們指定的Deferred . 即在隻有事務送出時才檢查該限制.

H:擷取關于限制系統資訊

可以通過查詢User_Constraints表獲得目前所有關于限制的系統資訊.下面關于限制User_Constraints表常用資訊字段說明:

Owner——限制所有者

Constraint_name——限制名稱

Constraint_Type——限制類型:C:代表Check或Not Null限制.  P:主鍵限制.   R:外鍵限制.  U:唯一限制.  V:Check option限制.  O:Readonly 隻讀限制

Table——name——限制定義針對表名

Status_限制的狀态 Enable或Disable 可用或不可用

Deferrable:是否為延遲限制 值為:Deferrable或Not Deferred.

Deferred:是否采用延遲 值為:IMMEDIate或Deferred.

1 --檢視全部的關于order_status'表限制資訊

2 select * from user_constraints where table_name='order_status' 

shangshu是關于表的限制. 對于表中特定的列隻需查詢User_cons_columns表即可 不在贅述.

本文轉自chenkaiunion 51CTO部落格，原文連結:http://blog.51cto.com/chenkai/765118