聊聊Oracle外來鍵約束（Foreign Key）的幾個操作選項

 

關係型資料庫是以資料表和關係作為兩大物件基礎。資料表是以二維關係將資料組織在DBMS中，而關係建立資料表之間的關聯，搭建現實物件模型。主外來鍵是任何資料庫系統都需存在的約束物件，從物件模型中的業務邏輯加以抽象，作為物理設計的一個部分在資料庫中加以實現。

Oracle外來鍵是維護參照完整性的重要手段，大多數情況下的外來鍵都是緊密關聯關係。外來鍵約束的作用，是保證字表某個欄位取值全都與另一個資料表主鍵欄位相對應。也就是說，只要外來鍵約束存在並有效，就不允許無參照取值出現在字表列中。具體在Oracle資料庫中，外來鍵約束還是存在一些操作選項的。本篇主要從實驗入手，介紹常見操作選項。

 

1、環境介紹

 

筆者選擇Oracle 11gR2進行測試，具體版本號為11.2.0.4。

 

 

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE      11.2.0.4.0     Production

TNS for 64-bit Windows: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

 

 

建立資料表Prim和Child，對應資料插入。

 

 

SQL> create table prim (v_id number(3), v_name varchar2(100));

Table created

 

SQL> alter table prim add constraint pk_prim primary key (v_id);

Table altered

 

SQL> create table child (c_id number(3), v_id number(3), c_name varchar2(100));

Table created

 

SQL> alter table child add constraint pk_child primary key (c_id);

Table altered

 

 

二、預設外來鍵行為

 

首先我們檢視預設外來鍵行為方式。

 

 

SQL> alter table CHILD

  2    add constraint FK_CHILD_PRIM foreign key (V_ID)

  3    references prim (V_ID)

  4  ;

 

 

在沒有額外引數加入的情況下，Oracle外來鍵將嚴格按照標準外來鍵方式工作。

 

 

--在有子記錄情況下，強制刪除主表記錄；

SQL> delete prim where v_id=2;

delete prim where v_id=2

 

ORA-02292:違反完整約束條件(A.FK_CHILD_PRIM) - 已找到子記錄

 

--在存在子表記錄情況下，更改主表記錄；

SQL> update prim set v_id=4 where v_id=2;

update prim set v_id=4 where v_id=2

 

ORA-02292:違反完整約束條件(A.FK_CHILD_PRIM) - 已找到子記錄

 

--修改子表記錄

SQL> update child set v_id=5 where v_id=2;

update child set v_id=5 where v_id=2

 

ORA-02291: 違反完整約束條件 (A.FK_CHILD_PRIM) - 未找到父項關鍵字

 

 

上面實驗說明：在預設的Oracle外來鍵配置條件下，只要有子表記錄存在，主表記錄是不允許修改或者刪除的。子表記錄也必須時刻保證參照完整性。

 

三、On delete cascade

 

對於應用開發人員而言，嚴格外來鍵約束關係是比較麻煩的。如果直接運算元據庫記錄，就意味著需要手工處理主子表關係，處理刪除順序問題。On delete cascade允許了一種“先刪除主表，連帶刪除子表記錄”的功能，同時確保資料表整體參照完整性。

建立on delete cascade外來鍵，只需要在建立外來鍵中新增相應的子句。

 

 

SQL> alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on delete cascade;

Table altered

 

 

測試：

 

 

SQL> delete prim where v_id=2;

1 row deleted

 

SQL> select * from prim;

V_ID V_NAME

---- --------------------------------------------------------------------------------

   1 kk

   3 iowkd

 

SQL> select * from child;

C_ID V_ID C_NAME

---- ---- --------------------------------------------------------------------------------

   1    1 kll

   2    1 ddkll

   3    1 43kll

 

SQL> rollback;

Rollback complete

 

 

刪除主表操作成功，對應的子表記錄被連帶自動刪除。但是其他操作依然是不允許進行。

 

 

SQL> update prim set v_id=4 where v_id=2;

update prim set v_id=4 where v_id=2

 

ORA-02292:違反完整約束條件(A.FK_CHILD_PRIM) - 已找到子記錄

 

SQL> update child set v_id=5 where v_id=2;

update child set v_id=5 where v_id=2

 

ORA-02291: 違反完整約束條件 (A.FK_CHILD_PRIM) - 未找到父項關鍵字

 

 

On delete cascade被稱為“級聯刪除”，對開發人員來講是一種方便的策略，可以直接“無視”子記錄而刪掉主記錄。但是，一般情況下，資料庫設計人員和DBA一般都不推薦這樣的策略。

究其原因，還是由於系統業務規則而定。On delete cascade的確在一定程度上很方便，但是這種自動操作在一些業務系統中是可能存在風險的。例如：一個系統中存在一個引數引用關係，這個引數被引用到諸如合同的主記錄中。按照業務規則，如果這個引數被引用過，就不應當被刪除。如果我們設定了on delete cascade外來鍵，連帶的合同記錄就自動的被“幹掉”了。開發引數模組的同事一般情況下，也沒有足夠的“覺悟”去做手工判定。基於這個因素，我們推薦採用預設的強約束關聯，起碼不會引起資料丟失的情況。

 

四、On Delete Set Null

 

除了直接刪除記錄，Oracle還提供了一種保留子表記錄的策略。注意：外來鍵約束本身不限制欄位為空的問題。如果一個外來鍵被設定為on delete set null，當刪除主表記錄的時候，無論是否存在子表對應記錄，主表記錄都會被刪除，子表對應列被清空。

 

 

SQL> alter table child drop constraint fk_child_prim;

Table altered

 

SQL> alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on delete set null;

Table altered

 

 

刪除主表記錄。

 

 

SQL> delete prim where v_id=2;

1 row deleted

 

SQL> select * from prim;

 

V_ID V_NAME

---- --------------------------------------------------------------------------------

   1 kk

   3 iowkd

 

SQL> select * from child;

 

C_ID V_ID C_NAME

---- ---- --------------------------------------------------------------------------------

   1    1 kll

   2    1 ddkll

   3    1 43kll

   4      43kll

   5      4ll

 

SQL> rollback;

Rollback complete

 

 

主表記錄刪除，子表外來鍵列被清空。其他約束動作沒有變化。

 

 

SQL> update prim set v_id=4 where v_id=2;

update prim set v_id=4 where v_id=2

 

ORA-02292:違反完整約束條件(A.FK_CHILD_PRIM) - 已找到子記錄

 

SQL> update child set v_id=5 where v_id=2;

update child set v_id=5 where v_id=2

 

ORA-02291: 違反完整約束條件 (A.FK_CHILD_PRIM) - 未找到父項關鍵字

 

 

那麼，下一個問題是：如果外來鍵列不能為空，會怎麼樣呢？

 

 

SQL> desc child;

Name   Type          Nullable Default Comments

------ ------------- -------- ------- --------

C_ID   NUMBER(3)                              

V_ID   NUMBER(3)     Y                        

C_NAME VARCHAR2(100) Y                         

 

SQL> alter table child modify v_id not null;

Table altered

 

SQL> desc child;

Name   Type          Nullable Default Comments

------ ------------- -------- ------- --------

C_ID   NUMBER(3)                              

V_ID   NUMBER(3)                               

C_NAME VARCHAR2(100) Y                        

 

 

SQL> delete prim where v_id=2;

delete prim where v_id=2

 

ORA-01407: 無法更新 ("A"."CHILD"."V_ID")為 NULL

 

 

更改失敗~

 

五、傳說中的on update cascade

 

On update cascade被稱為“級聯更新”，是關聯式資料庫理論中存在的一種外來鍵操作型別。這種型別指的是：當主表的記錄被修改（主鍵值修改），對應子表的外來鍵列值連帶的進行修改。

 

 

SQL> alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on update cascade;

 

alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on update cascade

 

ORA-00905: 缺失關鍵字

 

 

目前的Oracle版本中，似乎還不支援on update cascade功能。Oracle在官方服務中對這個問題的闡述是：在實際系統開發環境中，直接修改主鍵的情況是比較少的。所以，也許在將來的版本中，這個特性會進行支援。

 

六、結論

 

Oracle外來鍵是我們日常比較常見的約束型別。在很多專家和業界人員的討論中，我們經常聽到“使用外來鍵還是系統編碼”的爭論。支援外來鍵策略的一般都是資料庫專家和“大撒把”的設計師，藉助資料庫天然的特性，可以高效實現功能。支援系統編碼的人員大都是“物件派”等新派人員，相信可以藉助系統前端解決所有問題。

筆者對外來鍵的觀點是“適度外來鍵，雙重驗證”。外來鍵要設計在最緊密的引用關係中，對驗證動作，前端和資料庫端都要進行操作。外來鍵雖然可以保證最後安全渠道，但是不能將正確易於接受的資訊反饋到前端。前端開發雖然比較直觀，但是的確消耗精力。所以，把握適度是重要的出發點。