【轉載】Oracle限制(Constraint)詳解

​

Oracle限制(Constraint)詳解

概述

　　限制是資料庫用來確定資料滿足業務規則的手段，不過在真正的企業開發中，除了主鍵限制這類具有強需求的限制，像外鍵限制，檢查限制更多時候僅僅出現在資料庫設計階段，真實環境卻很少應用，更多是放到程式邏輯中去進行處理。這也比較容易了解，限制會一定程度上較低資料庫性能，有些規則直接在程式邏輯中處理就可以了，同時，也有可能在面對業務變更或是系統擴充時，資料庫限制會使得處理不夠友善。不過在我看來，資料庫限制是保證資料準确性的最後一道防線，對于設計合理的系統，處于性能考慮資料庫限制自然可有可無；不過若是面對關聯關系較為複雜的系統，且對系統而言，資料的準确性完整性要高于性能要求，那麼這些限制還是有必要的（否則，就會出現各種相對業務規則來說莫名其妙的髒資料，本人可是深有體會的。。）。總之，對于限制的選擇無所謂合不合理，需要根據業務系統對于準确性和性能要求的側重度來決定。

資料庫限制有五種：

•      主鍵限制（PRIMARY KEY）
•      唯一性限制（UNIQUE)
•      非空限制（NOT NULL)
•      外鍵限制（FOREIGN KEY)
•      檢查限制（CHECK)

下面我們就分别來看下這五類限制：

資料庫限制

主鍵限制（PRIMARY KEY)

     主鍵是定位表中單個行的方式，可唯一确定表中的某一行，關系型資料庫要求所有表都應該有主鍵，不過Oracle沒有遵循此範例要求，Oracle中的表可以沒有主鍵（這種情況不多見）。關于主鍵有幾個需要注意的點：

1. 鍵列必須必須具有唯一性，且不能為空，其實主鍵限制 相當于 UNIQUE+NOT NULL
2. 一個表隻允許有一個主鍵
3. 主鍵所在列必須具有索引（主鍵的唯一限制通過索引來實作），如果不存在，将會在索引添加的時候自動建立

     添加主鍵（限制的添加可在建表時建立，也可如下所示在建表後添加，一般推薦建表後添加，靈活度更高一些，建表時添加某些限制會有限制）

SQL> alter table emp add constraint emp_id_pk primary key(id);

唯一性限制（UNIQUE)

     唯一性限制可作用在單列或多列上，對于這些列或列組合，唯一性限制保證每一行的唯一性。

     UNIQUE需要注意：

1. 對于UNIQUE限制來講，索引是必須的。如果不存在，就自動建立一個（UNIQUE的唯一性本質上是通過索引來保證的）
2. UNIQUE允許null值，UNIQUE限制的列可存在多個null。這是因為，Unique唯一性通過btree索引來實作，而btree索引中不包含null。當然，這也造成了在where語句中用null值進行過濾會造成全表掃描。

     添加唯一限制

SQL> alter table emp add constraint emp_code_uq unique(code);

非空限制（NOT NULL)

非空限制作用的列也叫強制列。顧名思義，強制鍵列中必須有值，當然建表時候若使用default關鍵字指定了預設值，則可不輸入。

　　添加非空限制，文法較特别

SQL> alter table emp modify ename not null;

外鍵限制（FOREIGN KEY）

外鍵限制定義在具有父子關系的子表中，外鍵限制使得子表中的列對應父表的主鍵列，用以維護資料庫的完整性。不過出于性能和後期的業務系統的擴充的考慮，很多時候，外鍵限制僅出現在資料庫的設計中，實際會放在業務程式中進行處理。外鍵限制注意以下幾點：

1. 外鍵限制的子表中的列和對應父表中的列資料類型必須相同，列名可以不同
2. 對應的父表列必須存在主鍵限制（PRIMARY KEY）或唯一限制（UNIQUE）
3. 外鍵限制列允許NULL值，對應的行就成了孤行了

　　其實很多時候不使用外鍵，很多人認為會讓删除操作比較麻煩，比如要删除父表中的某條資料，但某個子表中又有對該條資料的引用，這時就會導緻删除失敗。我們有兩種方式來優化這種場景：

　　第一種方式簡單粗暴，删除的時候，級聯删除掉子表中的所有比對行，在建立外鍵時，通過 on delete cascade 子句指定該外鍵列可級聯删除：

SQL> alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept (deptno) on delete cascade;

　　第二種方式，删除父表中的對應行，會将對應子表中的所有比對行的外鍵限制列置為NULL，通過 on delete set null 子句實施：

SQL> alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno) on delete set null;

實際上，外鍵限制列和對應的父表列可以在同一張表中，常見的就是表的業務邏輯含義是一棵樹，最簡單的例子如下（id為主鍵id，fid為父id，fid存儲對id的引用），這種結構的表根據業務要求可通過Oracle的遞歸查詢來擷取這種層級關系

檢查限制（CHECK)

檢查限制可用來實施一些簡單的規則，比如列值必須在某個範圍内。檢查的規則必須是一個結果為true或false 的表達式，比如：

SQL> alter table emp add constraint emp_sex_ck check(sex in('男','女'));

限制狀态

　　很多時候由于業務需要，比如我們有大量的曆史資料，需要和現有資料合并，目前表存在資料庫限制（如非空限制），而這些曆史資料又包含違背非空限制的資料行，為了避免導入時由于違反限制而導入失敗，我們通過調整限制狀态來達到目的。

資料庫限制有兩類狀态

　　啟用/禁用（enable/disable）：是否對新變更的資料啟用限制驗證

　　驗證/非驗證 (validate/novalidate) ：是否對表中已客觀存在的資料進行限制驗證

這兩類四種狀态從文法角度講可以随意組合，預設是 enable validate

下面我們來看着四類組合會分别出現什麼樣的效果：

enable validate : 預設的限制組合狀态，無法添加違反限制的資料行，資料表中也不能存在違反限制的資料行；

enable novalidate : 無法添加違反限制的資料行，但對已存在的違反限制的資料行不做驗證；

disable validate : 可以添加違反限制的資料行，但對已存在的違反限制的資料行會做限制驗證（從描述中可以看出來，這本來就是一種互相沖突的限制組合，隻不過是文法上支援這種組合罷了，造成的結果就是會導緻DML失敗）

disable novalidate : 可以添加違法限制的資料行，對已存在的違反限制的資料行也不做驗證。

拿上面的例子來說，我們需要上傳大量違反非空限制的曆史資料（從業務角度講這些資料不會造成系統功能異常），可以臨時将限制狀态轉為 disable novalidate，以保證這些不合要求的資料導入表中

SQL> alter table emp modify constraint emp_ename_nn disable novalidate;

在資料導入完成之後，我們再将限制狀态轉為enable novalidate 以確定之後添加的資料不會再違反限制

SQL> alter table emp modify constraint emp_ename_nn enable novalidate;

總結

　　本文介紹了資料庫中的五類限制，也提到了資料庫限制的四種狀态組合，當你由于業務需求或是系統擴充，在一個限制嚴苛的系統中由于限制限制頻繁操作失敗的時候，不同組合的限制狀态或許能給你另一種處理方案。謝謝支援。