關(guān)系型
數(shù)據(jù)庫(kù)是以數(shù)據(jù)表和關(guān)系作為兩大對(duì)象基礎(chǔ)。數(shù)據(jù)表是以二維關(guān)系將數(shù)據(jù)組織在DBMS中，而關(guān)系建立數(shù)據(jù)表之間的關(guān)聯(lián)，搭建現(xiàn)實(shí)對(duì)象模型。主外鍵是任何數(shù)據(jù)庫(kù)系統(tǒng)都需存在的約束對(duì)象，從對(duì)象模型中的業(yè)務(wù)邏輯加以抽象，作為物理設(shè)計(jì)的一個(gè)部分在數(shù)據(jù)庫(kù)中加以實(shí)現(xiàn)。
Oracle外鍵是維護(hù)參照完整性的重要手段，大多數(shù)情況下的外鍵都是緊密關(guān)聯(lián)關(guān)系。外鍵約束的作用，是保證字表某個(gè)字段取值全都與另一個(gè)數(shù)據(jù)表主鍵字段相對(duì)應(yīng)。也就是說(shuō)，只要外鍵約束存在并有效，就不允許無(wú)參照取值出現(xiàn)在字表列中。具體在Oracle數(shù)據(jù)庫(kù)中，外鍵約束還是存在一些操作選項(xiàng)的。本篇主要從實(shí)驗(yàn)入手，介紹常見(jiàn)操作選項(xiàng)。
二、環(huán)境介紹
筆者選擇Oracle 11gR2進(jìn)行
測(cè)試，具體版本號(hào)為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 |
創(chuàng)建數(shù)據(jù)表Prim和Child，對(duì)應(yīng)數(shù)據(jù)插入。
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 |
二、默認(rèn)外鍵行為
首先我們查看默認(rèn)外鍵行為方式。
SQL> alter table CHILD
2 add constraint FK_CHILD_PRIM foreign key (V_ID)
3 references prim (V_ID)
4 ;
在沒(méi)有額外參數(shù)加入的情況下，Oracle外鍵將嚴(yán)格按照標(biāo)準(zhǔn)外鍵方式
工作。
--在有子記錄情況下，強(qiáng)制刪除主表記錄；
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) - 未找到父項(xiàng)關(guān)鍵字
上面實(shí)驗(yàn)說(shuō)明：在默認(rèn)的Oracle外鍵配置條件下，只要有子表記錄存在，主表記錄是不允許修改或者刪除的。子表記錄也必須時(shí)刻保證參照完整性。
三、On delete cascade
對(duì)于應(yīng)用開(kāi)發(fā)人員而言，嚴(yán)格外鍵約束關(guān)系是比較麻煩的。如果直接操作數(shù)據(jù)庫(kù)記錄，就意味著需要手工處理主子表關(guān)系，處理刪除順序問(wèn)題。On delete cascade允許了一種“先刪除主表，連帶刪除子表記錄”的功能，同時(shí)確保數(shù)據(jù)表整體參照完整性。
創(chuàng)建on delete cascade外鍵，只需要在創(chuàng)建外鍵中添加相應(yīng)的子句。
SQL> alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on delete cascade;
Table altered
測(cè)試：
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 |
刪除主表操作成功，對(duì)應(yīng)的子表記錄被連帶自動(dòng)刪除。但是其他操作依然是不允許進(jìn)行。
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) - 未找到父項(xiàng)關(guān)鍵字
On delete cascade被稱為“級(jí)聯(lián)刪除”，對(duì)開(kāi)發(fā)人員來(lái)講是一種方便的策略，可以直接“無(wú)視”子記錄而刪掉主記錄。但是，一般情況下，數(shù)據(jù)庫(kù)設(shè)計(jì)人員和DBA一般都不推薦這樣的策略。
究其原因，還是由于系統(tǒng)業(yè)務(wù)規(guī)則而定。On delete cascade的確在一定程度上很方便，但是這種自動(dòng)操作在一些業(yè)務(wù)系統(tǒng)中是可能存在風(fēng)險(xiǎn)的。例如：一個(gè)系統(tǒng)中存在一個(gè)參數(shù)引用關(guān)系，這個(gè)參數(shù)被引用到諸如合同的主記錄中。按照業(yè)務(wù)規(guī)則，如果這個(gè)參數(shù)被引用過(guò)，就不應(yīng)當(dāng)被刪除。如果我們?cè)O(shè)置了on delete cascade外鍵，連帶的合同記錄就自動(dòng)的被“干掉”了。開(kāi)發(fā)參數(shù)模塊的同事一般情況下，也沒(méi)有足夠的“覺(jué)悟”去做手工判定?；谶@個(gè)因素，我們推薦采用默認(rèn)的強(qiáng)約束關(guān)聯(lián)，起碼不會(huì)引起數(shù)據(jù)丟失的情況。
四、On Delete Set Null
除了直接刪除記錄，Oracle還提供了一種保留子表記錄的策略。注意：外鍵約束本身不限制字段為空的問(wèn)題。如果一個(gè)外鍵被設(shè)置為on delete set null，當(dāng)刪除主表記錄的時(shí)候，無(wú)論是否存在子表對(duì)應(yīng)記錄，主表記錄都會(huì)被刪除，子表對(duì)應(yīng)列被清空。
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 |
主表記錄刪除，子表外鍵列被清空。其他約束動(dòng)作沒(méi)有變化。
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) - 未找到父項(xiàng)關(guān)鍵字
那么，下一個(gè)問(wèn)題是：如果外鍵列不能為空，會(huì)怎么樣呢？
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: 無(wú)法更新 ("A"."CHILD"."V_ID")為 NULL |
更改失敗~
五、傳說(shuō)中的on update cascade
On update cascade被稱為“級(jí)聯(lián)更新”，是關(guān)系數(shù)據(jù)庫(kù)理論中存在的一種外鍵操作類型。這種類型指的是：當(dāng)主表的記錄被修改（主鍵值修改），對(duì)應(yīng)子表的外鍵列值連帶的進(jìn)行修改。
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: 缺失關(guān)鍵字
目前的Oracle版本中，似乎還不支持on update cascade功能。Oracle在官方服務(wù)中對(duì)這個(gè)問(wèn)題的闡述是：在實(shí)際系統(tǒng)開(kāi)發(fā)環(huán)境中，直接修改主鍵的情況是比較少的。所以，也許在將來(lái)的版本中，這個(gè)特性會(huì)進(jìn)行支持。
六、結(jié)論
Oracle外鍵是我們?nèi)粘１容^常見(jiàn)的約束類型。在很多專家和業(yè)界人員的討論中，我們經(jīng)常聽(tīng)到“使用外鍵還是系統(tǒng)編碼”的爭(zhēng)論。支持外鍵策略的一般都是數(shù)據(jù)庫(kù)專家和“大撒把”的設(shè)計(jì)師，借助數(shù)據(jù)庫(kù)天然的特性，可以高效實(shí)現(xiàn)功能。支持系統(tǒng)編碼的人員大都是“對(duì)象派”等新派人員，相信可以借助系統(tǒng)前端解決所有問(wèn)題。
筆者對(duì)外鍵的觀點(diǎn)是“適度外鍵，雙重驗(yàn)證”。外鍵要設(shè)計(jì)在最緊密的引用關(guān)系中，對(duì)驗(yàn)證動(dòng)作，前端和數(shù)據(jù)庫(kù)端都要進(jìn)行操作。外鍵雖然可以保證最后安全渠道，但是不能將正確易于接受的信息反饋到前端。前端開(kāi)發(fā)雖然比較直觀，但是的確消耗精力。所以，把握適度是重要的出發(fā)點(diǎn)。