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# integerprimarykey, sname varchar2(20), age integer);
            
        

(2)课程表，属性有课程号(主键)、课程名和学分：

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

(3)成绩表，属性有学号、课程号和分数，学号是学生表中学号外键、课程号是课程表中课程号外键：

            
                
                    create
                    table SC (
       S# integerforeignkey (S#) references Student(S#) ondeletecascade
       C# integerforeignkey (C#) references Course(C#) ondeletecascade
       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 foreignkey (S#) references Student(S#) ondeletecascade,
       constraint cidfk foreignkey (C#) references Course(C#) ondeletecascade
);
            
        

这里两个外键分别命名为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 ininteger, S#_new ininteger)
       asbeginexecuteimmediate‘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 ‘altertable SC enable constraint sidfk‘;
end;