Oracle預存程序update受外鍵約束的主索引值時完整性衝突解決方式

標籤：

1.問題背景

儘管在資料庫操作中我們並不提倡改動主鍵，可是確實在實際生活中有這種業務需求：

表A有主鍵KA，表B中聲明了一個references A(KA)的外鍵約束。我們須要改動A中某條目KA的值而且更新B中外鍵約束。

可是DBMS在運行了第一條update後檢查完整性會發現衝突：B中條目的外鍵不存在。

註：我在Oracle database環境下遇到這個問題的。Oracle非常蛋疼的不能設定外鍵為update級連。所以僅僅有人工處理。

2.舉例說明

用一個簡單的範例說明。資料庫中有下面三個表：
(1)學生表。屬性有學號(主鍵)、姓名和年齡：

create table Student(S# integer primary key, sname varchar2(20), age integer);

(2)課程表，屬性有課程號(主鍵)、課程名和學分：

create table Course(C# integer primary key, cname varchar2(20), credit integer);

(3)成績表，屬性有學號、課程號和分數，學號是學生表中學號外鍵、課程號是課程表中課程號外鍵：

create table SC (       S# integer foreign key (S#) references Student(S#) on delete cascade       C# integer foreign key (C#) references Course(C#) on delete cascade       score integer);

我們須要改動一個學生的學號，假設成績表中存在改學生的成績條目，那麼就會引發上述完整性衝突。

3.解決方式

我想到的思路有兩個：

• 屏蔽（或刪除）SC表外鍵約束，改動Student表學號，而且在保證一致性（我們DBA來保證）的情況下更新全部SC中該學生的學號，最後恢複（或加入）SC表外鍵約束。

• 取出SC中全部該學生的成績條目放在零時表/外部變數中然後刪除SC中的這些記錄，改動Student表學號，而且在保證一致性（相同我們DBA保證）的情況下改動零時表/外部變數中資料後再全部插入SC表。

前一個方法（屏蔽改動再恢複）比較簡單。下面進一步解說步驟：

1. 我們須要改動下面SC表中外鍵聲明，加入外鍵約束的名字，以方便我們興許屏蔽和恢複外鍵約束：

create table SC (       S# integer,       C# integer,       score integer,       constraint sidfk foreign key (S#) references Student(S#) on delete cascade,       constraint cidfk foreign key (C#) references Course(C#) on delete cascade);

這裡兩個外鍵分別命名為sidfk和cidfk。
2. 屏蔽和開啟外鍵約束：
用SQL alter table語句實現屏蔽和開啟。設S#_new是新學號，S#_old是老學號：

alter table SC disable constraint sidfk;update Student set S# = S#_new where S# = S#_old;update SC set S# = S#_new where S# = S#_old;alter table SC enable constraint sidfk;

3.在Oracle上用預存程序實現
因為Oracle預存程序中不能直接使用create table或者alter table一類改動表結構的語句。需用execute immediate + SQL Command動態調用。
完整的儲存步驟例如以下：

create or replace procedure ChangeStuId(S#_old in integer, S#_new in integer)       asbegin           execute immediate ‘alter table SC disable constraint sidfk‘;        update Student set S# = S#_new where S# = S#_old;        update SC set S# = S#_new where S# = S#_old;        execute immediate ‘alter table SC enable constraint sidfk‘;end;

Oracle預存程序update受外鍵約束的主索引值時完整性衝突解決方式