Oracle存储过程update受外键约束的主键值时完整性冲突解决方式
2024-10-18 23:31:33
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;
最新文章
- 【资源】108个大数据文档PDF开放下载-整理后打包下载
- #essay 161218# 自己的markdown笔记(日记)方法
- PPT设计宝典!十招教你做出拿得出手的PPT
- iOS开发小技巧--巧用ImageView中的mode(解决图片被拉伸的情况)
- 存储过程使用CTE 和 case when
- 定期来一次“绩效谈话”(摘自易中)
- missing locales
- 虚方法(virtual)和抽象方法(abstract)的区别
- Item Import: What Does ";Sync"; Items Do? (Doc ID 417887.1)
- MobileProject
- 序列!序列!- 零基础入门学习Python016
- 在昆明网络SEO的走向站外的优化该何去何从?
- Gauge----自动化测试工具
- cordova本地浮动框提示插件使用:cordova-plugin-x-toast
- mybatis学习1
- HDOJ 5666//快速积,推公式
- token和盐
- pytorch入门与实践-2.2-CIFAR10分类网络
- MySQL 一些内部原理
- centos6.9 安装完xampp 7.2.0后,执行/opt/lampp/lampp报错
热门文章
- ***codeigniter操作xml(Simplexml第三方扩展)
- head命令 tail命令
- 在jenkins里使用SCM管理jenkinsfile
- CCF CSP 201612-3 权限查询
- HBase(三)HBase架构与工作原理
- Asp.net vNext 学习之路(三)
- 【LOJ】#2542. 「PKUWC2018」随机游走
- python错误:UnicodeDecodeError: 'utf8' codec can't decode byte 0xe6 in position 0: unexpected end of data
- 006 python的面向对象基础
- Filter的应用--权限过滤