Oracle存储过程update受外键约束的主键值时完整性冲突解决方案
来源:互联网 发布:mysql frm hy000 编辑:程序博客网 时间:2024/05/19 13:29
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) 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;
0 0
- Oracle存储过程update受外键约束的主键值时完整性冲突解决方案
- ORACLE的完整性约束
- oracle的完整性约束
- UPDATE 时主键冲突引发的思考
- oracle数据的完整性约束
- MySQL UPDATE时主键冲突引发的思考
- VC++ 表格、完整性约束、视图、触发器、存储过程的创建SQL语句
- 【转】一篇关于Oracle的完整性约束
- Oracle 完整性约束(转摘)
- Oracle数据库完整性约束
- oracle完整性约束
- oracle 完整性约束
- Oracle完整性约束
- oracle数据完整性约束
- 【oracle】完整性约束
- Oracle数据库完整性约束
- Oracle 数据完整性,约束
- MySQL的完整性约束之:实体完整性(主键的添加)
- 北大光华学院学生研究报告-关于超级表格创业
- POJ 1337 A Lazy Worker(DP)
- 3-6
- struts2学习笔记之十六(token标签)
- 单例设计模式 浅谈
- Oracle存储过程update受外键约束的主键值时完整性冲突解决方案
- HTML锚点&JSP锚点
- 第六章习题汇总
- Median of Two Sorted Arrays
- 浏览一天的收获
- 代码学习-Linux内核网卡收包过程(NAPI)
- HDU--2032
- Hadoop技术预览
- 用VS2012创建和使用WebService(连接SQL Server)