主外键关联表的数据删除策略

说个题外话,我从来没想过会被拼多多这款软件所魔怔,他的这种社交营销的能力,实在是太强了,在怂恿之下,开始给儿子拼个这个,


各位有空帮砍下,https://w.url.cn/s/AzlPAB9,或者扫下码,

言归正传,看到老杨写的这篇小文《ORA-02292_主表在有子表外键约束的情况,如何快速批量删除和插入数据?》,很有感触,在Oracle中,主外键关系,是看似简单,实则蕴含着很多的知识。

和外键相关的历史文章:

探究外键为何要建索引?

外键为何要建索引?

ORA-02266错误解决方案

解决导入过程中出现的ORA-02289错误

原文如下。

【引言】

最近接手一小活,需求要将一张表中的数据进行全部更新,因主表有外键关联,无法删除本表中数据,即删除有外键的表的数据时,Oracle会提示:ORA-02292:integrity constraint(xx) violated - child record found,这种情况下咋个办?本文讲解之。

 

针对上述需求,有三种解决方案:

方案1

在不更改主表和子表约束条件的前提下,先删除映射主表主键的相对应的子表中数据行后,删除主表中数据,接着对主表批量insert操作,因第一步子表中已删除的数据业务还需使用,故最后一步需将子表中的对应的数据insert回子表。

 

此方法应用场景适用在主表需更新数据量少的情况下;难点是在主表数据量大的情况下,涉及到子表相应行的删除和回插操作很是繁琐,导致的业务中断时间较长,数据复核时间较长。

 

方案2:

在不更改主表和子表约束条件的前提下,将主表的待更新数据行进行逐行的update操作。如每行的更新列不统一,导致的update操作会异常繁多,不亚于方案1。如因本案例中每行的更新列均不尽同,具体的update语句人工逐个更改,难度较大,纯属拼体力劳动,故不推荐此法。

 

方案3:

先是的主表关联主键约束条件失效,删除主表中数据后,批量insert数据后,再让其主键约束生效。此方案可快速实现主表的批量删除、insert级联数据的需求。

 

经过对比主表原有数据集和待导入数据集,新导入的数据集包含现有主库数据集,故本文采用简单粗暴的方案3。

 

具体操作之前,先简介约束条件的启停命令和含义:

启用约束
enable (validate):启用约束,创建索引,对已有及新加入的数据执行约束。
enable novalidate:启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据。


禁用约束:
disable (novalidate):关闭约束,删除索引,可以对约束列的数据进行修改等操作。
disable validate:关闭约束,删除索引,不能对表进行插入/更新/删除等操作。

 

主键约束的相关知识点:

1. 建立唯一约束或主键索约束,会同时自动创建一个与之同名的索引;

2. 如果disable约束,与之对应的索引会自动删除掉;

3. 如想插入重复数据,要暂时违反或禁用唯一约束,后来再enable就会报违反唯一约束。

具体操作步骤如下:

1. 首先查询表currency表的数据量,

ethan_user@ethanDB> select count(*) from currency;  
COUNT(*)
----------       
500

2. 先不做任何操作,执行下delete一行的操作,看下报错信息

ethan_user@ethanDB> delete from currency where CURRENCY_CODE='AED';
delete from currency where CURRENCY_CODE='AED'
*
ERROR at line 1:ORA-02292: integrity constraint (ethan_user.FKCOUNTRY_CURRENCY) violated - childrecord found

查看约束条件ethan_user.FKCOUNTRY_CURRENCY的具体信息,其中表country是表currency的子表,constraint_type为“R”,即为外键约束。

ethan_user@ethanDB> select count(*) from country;   
COUNT(*)
----------       
250

3. 让主表的主键失效,从而让子表中的外键依赖关系失效,

1. 主键失效:alter table table_name disable primary key cascade;
2. 删除数据:delete from  table_name  where id = 'xx';
3. 主键重新生效:alter table table_name enable primary key;

执行步骤如下:

步骤1:主键失效

通过如下主表currency的定义,查看主键约束为 CURRENCY_P,

ethan_user@ethanDB> set pagesize 0
ethan_user@ethanDB> set long 9999
ethan_user@ethanDB> select dbms_metadata.get_ddl('TABLE','CURRENCY') from dual;

或者执行如下语句,使得表currency的主键失效,

ethan_user@ethanDB> ALTER TABLE ethan_user.CURRENCY
  MODIFY CONSTRAINT CURRENCY_P
  DISABLE
  NOVALIDATE;

或者执行,

ethan_user@ethanDB> alter table currency disable primary key cascade;
Table altered.


步骤2:删除主表数据

ethan_user@ethanDB> delete from currency where CURRENCY_CODE='AED';
1 row deleted.
ethan_user@ethanDB> commit;
Commit complete.

步骤3:主键重新生效

ethan_user@ethanDB> alter table currency enable primary key;
Table altered.

或者执行,

ethan_user@ethanDB> ALTER TABLE ethan_user.CURRENCY
  MODIFY CONSTRAINT CURRENCY_P
  ENABLE
  VALIDATE;

确认主表currency的逐渐约束是否生效,

ethan_user@ethanDB> select constraint_name,constraint_type ,status,table_name from user_constraints where constraint_name='CURRENCY_P';

通过以上测试,我们得到的是,

1. 本文记录了主表在有子表外键约束的情况,如何快速批量删除和插入数据;

2. 约束条件constraint的失效和生效,本文使用了两种方式,推荐使用enable/disable novalidate/validate 的方式,可更加灵活的控制约束条件的生效与否。Alter table table_name disable primary key的方式好处在于不用先查明主键约束的名称,操控性上较为便捷。

近期的热文:

海底的下面究竟有什么?

几种去重的SQL写法

打造国产技术产品的必要性

六一儿童节带给我们的思考

SQL查询总是先执行SELECT语句么?

Oracle删除字段的方式和风险,你都了解么?

最烧脑的珠峰高程测算过程

了解阿克曼转向原理的作用

《你就是孩子最好的玩具》学习笔记 - 第一章

登录缓慢的诡异问题

不可不知的7个JDK命令

一个Full GC次数过多导致系统CPU 100%的案例排查

ASCII码作用简介

Java GC的基础知识

Linux下的^M困惑

Oracle相关提问的智慧技巧

很久以前的一篇对初学Oracle建议的文章

PLSQL Developer几个可能的隐患

从70万字SRE神作提炼出的7千字精华文章

从数据误删到全量恢复的惊险记录
公众号600篇文章分类和索引

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值