Oracle存储过程update受外键约束的主键值时完整性冲突解决方式

时间：2021-07-06 22:31:46

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)

       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;

相关文章