一个诡异的SQL事务现象

>>强大,10k+点赞的 SpringBoot 后台管理系统竟然出了详细教程!

今天测试过程中,同事提出了一个,看似诡异,实则很基础的问题,乍一看会被迷惑。


用实验来复现下这个问题,

(1) 创建测试表,A表的id字段是主键,B表的id_a字段是外键,参考A表的id主键,

一个诡异的SQL事务现象


(2) 应用有这么一个逻辑,一个事务中,先更新表A,再INSERT表B,其中表B的id_a字段值是来自于表A刚才操作的主键,模拟如下,

一个诡异的SQL事务现象

可以看出,更新表A的操作正常,但使用表A的主键值id=1,来INSERT表B的时候,报了FK_B_A外键完整性约束的错误。


明明A表有id=1的记录,并且更新UPDATE操作成功了,为什么用id_a=1来INSERT表B,提示了外键完整性约束错误,其含义就是无法从主表找出字表要INSERT的外键值id=1,两者相矛盾么?


此处为分割线,朋友们可以思考下,为什么会有这种问题?




使用log miner利器,挖掘下redo日志,发现这张表曾经做过rename操作,

一个诡异的SQL事务现象


此时检索下约束信息,表B的外键约束FK_B_A,即ID_A字段,参考引用的是约束PK_A,

一个诡异的SQL事务现象


记得没错的话,PK_A是表A的主键字段id,可实际上,约束PK_A是表A_BAK表的主键字段列id,

一个诡异的SQL事务现象


原因就是之前rename了表A为表A_BAK,虽然表名变了,但表上的约束名称未变,因此表B的外键参考的表名,从表A变为了表A_BAK。


如果此时删除A_BAK可以么?

一个诡异的SQL事务现象


报错的原因是因为有子表参考引用了这张表的唯一键/主键,和删除数据相同,必须从子表开始操作,关系干净了,然后才能操作主表。

一个诡异的SQL事务现象


啰嗦几句,这里使用了drop,其实11g下这些对象并为真正删除,而是放入了回收站,

一个诡异的SQL事务现象

可以看出,表B、表A_BAK以及表A_BAK的主键索引,这些对象名均被改写了。


约束名称也同时被改了,

一个诡异的SQL事务现象


不变的则是表的字段列,

一个诡异的SQL事务现象


如果不想存回收站了,直接删除,则可以使用purge属性。



总结:

1. 凡是有主外键约束的表,无论删除数据还是删除表,均需要从子表开始,所有子表清理干净了,才能继续操作主表数据。

2. 任何看似诡异的现象背后,都有他存在的原因,即哲学所说的“存在即合理”。


顺便打一个小广告了,之前写了一篇小文《国安2:0贵州》,有幸被中赫国安官方授权的公众号“MAX国安“选中,作为一期推文,欢迎各位点击、指教、分享,链接地址是:《不只是一篇赛后点评,国安球迷读到最后涨知识》。

P.S. 点击量超过5000,稿费翻番。。。



如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

一个诡异的SQL事务现象