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

男娘i 2023-02-17 03:41 21阅读 0赞

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

![format_png][]

各位有空帮砍下，https://w.url.cn/s/AzlPAB9，或者扫下码，![format_png 1][]

![format_png 2][]

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

和外键相关的历史文章：

《[探究外键为何要建索引？][Link 1]》

《[外键为何要建索引？][Link 2]》

《[ORA-02266错误解决方案][ORA-02266]》

《[解决导入过程中出现的ORA-02289错误][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的方式好处在于不用先查明主键约束的名称，操控性上较为便捷。

**近期的热文：**

《[海底的下面究竟有什么？][Link 3]》

《[几种去重的SQL写法][SQL]》

《[打造国产技术产品的必要性][Link 4]》

《[六一儿童节带给我们的思考][Link 5]》

《[SQL查询总是先执行SELECT语句么？][SQL_SELECT]》

《[Oracle删除字段的方式和风险，你都了解么？][Oracle]》

《[最烧脑的珠峰高程测算过程][Link 6]》

《[了解阿克曼转向原理的作用][Link 7]》

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

《[登录缓慢的诡异问题][Link 8]》

《[不可不知的7个JDK命令][7_JDK]》

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

《[ASCII码作用简介][ASCII]》

《[Java GC的基础知识][Java GC]》

《[Linux下的^M困惑][Linux_M]》

《[Oracle相关提问的智慧技巧][Oracle 1]》

《[很久以前的一篇对初学Oracle建议的文章][Oracle 2]》

《[PLSQL Developer几个可能的隐患][PLSQL Developer]》

《[从70万字SRE神作提炼出的7千字精华文章][70_SRE_7]》

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

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYW1TbjFyUTRqS0JTTFIzN25uVEwzb1VmNmF6RnhOeDdTNE1kWXZwYXN0UXpaRkFVd3ZuaWJsNEltVFVMN1JubmljZjhtNG1hdGplVHgyUS82NDA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYW1TbjFyUTRqS0JTTFIzN25uVEwzb1VJMU9EVURsQkR4RktORmVSWDZYcmdWbXR2UDZSeVlkV3ByTnZYMGpKWkZzOUEyU1BLTUFUbEEvNjQw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYW1TbjFyUTRqS0JTTFIzN25uVEwzb1VDcDlEcWxteWg5UDNnRDV5cXJpYlZtNGh3a2Z6Tk1kN01lM2VpYnQ4MmhuQUlJdHh6dHEwa0FpYWcvNjQw?x-oss-process=image/format,png
[ORA-02292]: http://mp.weixin.qq.com/s?__biz=Mzg2NTI0NDE1Mg%3D%3D&chksm=ce5c4e85f92bc79348a654c886d29cb4c4bd5b17c839b4782dbd20dfc29a0f95660739c22609&idx=1&mid=2247485458&scene=21&sn=a6160d63957302deccd3c1654e49cc75#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814eabddf6c7bdada45c4b1030a360d1c24a9a9f437d15d286a02af4855d4ab41901650ca8&idx=1&mid=2247486296&scene=21&sn=c06860bf9333d1e0a56f5caa5ee2433f#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea8144edddf6cdfb32454d8398b24dd0826bf8dc23afbe92ce548a58a18eebc7710497e88ae5&idx=1&mid=2247483678&scene=21&sn=e7ef704e21ceba5cb149c7ebae90d0bf#wechat_redirect
[ORA-02266]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814f00ddf6c616fc49f5e6163106ff85b2a1cccdba068a350742459acee7af09777e03806d&idx=1&mid=2247486195&scene=21&sn=db8c7913f2a4814589967abd83511f40#wechat_redirect
[ORA-02289]: https://blog.csdn.net/bisal/article/details/77890461
[Link 3]: https://blog.csdn.net/bisal/article/details/106654180
[SQL]: https://blog.csdn.net/bisal/article/details/106631874
[Link 4]: https://blog.csdn.net/bisal/article/details/106512908
[Link 5]: https://blog.csdn.net/bisal/article/details/106485013
[SQL_SELECT]: https://blog.csdn.net/bisal/article/details/106416527
[Oracle]: https://blog.csdn.net/bisal/article/details/106345687
[Link 6]: https://blog.csdn.net/bisal/article/details/106293626
[Link 7]: https://blog.csdn.net/bisal/article/details/106232934
[-]: https://blog.csdn.net/bisal/article/details/106232933
[Link 8]: https://blog.csdn.net/bisal/article/details/106204880
[7_JDK]: https://blog.csdn.net/bisal/article/details/106132095
[Full GC_CPU 100]: https://blog.csdn.net/bisal/article/details/106110289
[ASCII]: https://blog.csdn.net/bisal/article/details/106088569
[Java GC]: https://blog.csdn.net/bisal/article/details/106066216
[Linux_M]: https://blog.csdn.net/bisal/article/details/106030314
[Oracle 1]: https://blog.csdn.net/bisal/article/details/106009871
[Oracle 2]: https://blog.csdn.net/bisal/article/details/105963320
[PLSQL Developer]: https://blog.csdn.net/bisal/article/details/105803468
[70_SRE_7]: https://blog.csdn.net/bisal/article/details/105742900
[Link 9]: https://blog.csdn.net/bisal/article/details/105697127
[600]: https://blog.csdn.net/bisal/article/details/104548761