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

Oracle預存程序update受外鍵約束的主索引值時完整性衝突解決方案，oracle預存程序
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表。

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

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;