雖然在數據庫操作中我們並不提倡修改主鍵,但是確實在實際生活中有這樣的業務需求:
表A有主鍵KA,表B中聲明了一個references A(KA)的外鍵約束,我們需要修改A中某條目KA的值並且更新B中外鍵約束。
但是DBMS在執行了第一條update後檢查完整性會發現沖突:B中條目的外鍵不存在。
注:我在Oracle database環境下遇到這個問題的,Oracle很蛋疼的不能設置外鍵為update級連,所以只有人工處理。
用一個簡單的例子說明,數據庫中有以下三個表:
(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
);
我們需要修改一個學生的學號,如果成績表中存在改學生的成績條目,那麼就會引發上述完整性沖突。
我想到的思路有兩個:
屏蔽(或刪除)SC表外鍵約束,修改Student表學號,並且在保證一致性(我們DBA來保證)的情況下更新所有SC中該學生的學號,最後恢復(或添加)SC表外鍵約束。 取出SC中所有該學生的成績條目放在零時表/外部變量中然後刪除SC中的這些記錄,修改Student表學號,並且在保證一致性(同樣我們DBA保證)的情況下修改零時表/外部變量中數據後再全部插入SC表。前一個方法(屏蔽修改再恢復)比較簡單,下面進一步講解步驟:
我們需要修改以下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)
as
begin
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;