程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle存儲過程update受外鍵約束的主鍵值時完整性沖突解決方案

Oracle存儲過程update受外鍵約束的主鍵值時完整性沖突解決方案

編輯:Oracle教程

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表。

前一個方法(屏蔽修改再恢復)比較簡單,下面進一步講解步驟:

我們需要修改以下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;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved