ORA-02292_主表在有子表外鍵約束的情況如何快速批量刪除和插入數(shù)據(jù)？

1、【引言】最近接手一小活，需求要將一張表中的數(shù)據(jù)進(jìn)行全部更新，因主表有外鍵關(guān)聯(lián)，無法刪除本表中數(shù)據(jù)，即刪除有外鍵的表的數(shù)據(jù)時，oracle會提示:ORA-02292:integrity constraint(xx) violated - child record found，這種情況下咋個辦？本文講解之。針對上述需求，有三種解決方案：方案1：在不更改父表和子表約束條件的前提下，先刪除映射主表主鍵的相對應(yīng)的子表中數(shù)據(jù)行后，刪除父表中數(shù)據(jù)，接著對主表批量insert操作，因第一步子表中已刪除的數(shù)據(jù)業(yè)務(wù)還需使用，故最后一步需將子表中的對應(yīng)的數(shù)據(jù)insert回子表。此方法應(yīng)用場景適用在主表需更新數(shù)據(jù)量少

2、的情況下；難點是在主表數(shù)據(jù)量大的情況下，涉及到子表相應(yīng)行的刪除和回插操作很是繁瑣，導(dǎo)致的業(yè)務(wù)中斷時間較長，數(shù)據(jù)復(fù)核時間較長。方案2：在不更改父表和子表約束條件的前提下，將父表的待更新數(shù)據(jù)行進(jìn)行逐行的update操作。如每行的更新列不統(tǒng)一，導(dǎo)致的update操作會異常繁多，不亞于方案1。如因本案例中每行的更新列均不盡同，具體的update語句人工逐個更改，難度較大，純屬拼體力勞動，故不推薦此法。方案3：先是的主表關(guān)聯(lián)主鍵約束條件失效，刪除主表中數(shù)據(jù)后，批量insert數(shù)據(jù)后，再讓其主鍵約束生效。此方案可快速實現(xiàn)主表的批量刪除、insert級聯(lián)數(shù)據(jù)的需求。經(jīng)過對比主表原有數(shù)據(jù)集和待導(dǎo)入數(shù)據(jù)集，新導(dǎo)

3、入的數(shù)據(jù)集包含現(xiàn)有主庫數(shù)據(jù)集，故本文采用簡單粗暴的方案3。具體操作之前，先簡介約束條件的啟停命令和含義:enable( validate) :啟用約束,創(chuàng)建索引,對已有及新加入的數(shù)據(jù)執(zhí)行約束。enable novalidate :啟用約束,創(chuàng)建索引,僅對新加入的數(shù)據(jù)強制執(zhí)行約束,而不管表中的現(xiàn)有數(shù)據(jù)。禁用約束:disable( novalidate):關(guān)閉約束,刪除索引,可以對約束列的數(shù)據(jù)進(jìn)行修改等操作。disable validate :關(guān)閉約束,刪除索引,不能對表進(jìn)行插入/更新/刪除等操作。主鍵約束的相關(guān)知識點：1. 建立唯一約束或主鍵索約束，會同時自動創(chuàng)建一個與之同名的索引；2. 如果d

4、isable約束，與之對應(yīng)的索引會自動刪除掉；3. 如想插入重復(fù)數(shù)據(jù)，要暫時違反或禁用唯一約束，后來再enable就會報違反唯一約束。具體操作步驟如下：1. 首先查詢表currency表的數(shù)據(jù)量ethan_userethanDB> select count(*) from currency; COUNT(*)- 1842. 先不做任何操作，執(zhí)行下delete一行的操作，看下報錯信息ethan_userethanDB> delete from currency where CURRENCY_CODE='AED'delete from currency where CU

5、RRENCY_CODE='AED'*ERROR at line 1:ORA-02292: integrity constraint (ethan_user.FKCOUNTRY_CURRENCY) violated - childrecord found查看約束條件ethan_user.FKCOUNTRY_CURRENCY的具體信息ethan_userethanDB> select constraint_name,constraint_type ,status,table_name from user_constraints where constraint_name=&#

6、39;FKCOUNTRY_CURRENCY'CONSTRAINT_NAME-C STATUS- -TABLE_NAME-FKCOUNTRY_CURRENCYR ENABLEDCOUNTRY可以看出表country是表currency的子表，constraint_type為“R”，即為外鍵約束。ethan_userethanDB> select count(*) from country; COUNT(*)- 2483. 讓父表的主鍵失效，從而讓子表中的外鍵依賴關(guān)系失效1.）主鍵失效：alter table table_name disable primary key cascad

7、e;2.）刪除數(shù)據(jù)： delete from  table_name  where id = 'xx'3.）主鍵重新生效：alter table table_name enable primary key;執(zhí)行步驟如下：步驟1：主鍵失效通過如下主表currency的定義，查看主鍵約束為 CURRENCY_Pethan_userethanDB> set pagesize 0ethan_userethanDB> set long 9999ethan_userethanDB> select dbms_metadata.get_ddl('T

8、ABLE','CURRENCY') from dual;或者執(zhí)行如下語句，使得表currency的主鍵失效ethan_userethanDB> ALTER TABLE ethan_user.CURRENCY MODIFY CONSTRAINT CURRENCY_P DISABLE NOVALIDATE;或者執(zhí)行ethan_userethanDB> alter table currency disable primary key cascade;Table altered.查看表currency的主鍵狀態(tài)ethan_userethanDB> select

9、 constraint_name,constraint_type ,status,table_name from user_constraints where constraint_name='CURRENCY_P'CONSTRAINT_NAME-C STATUS- -TABLE_NAME-CURRENCY_PP DISABLEDCURRENCY步驟2：刪除主表數(shù)據(jù)ethan_userethanDB> delete from currency where CURRENCY_CODE='AED'1 row deleted.ethan_userethanDB&

10、gt; commit;Commit complete.步驟2：主鍵重新生效ethan_userethanDB> alter table currency enable primary key;Table altered.或者執(zhí)行ethan_userethanDB> ALTER TABLE ethan_user.CURRENCY MODIFY CONSTRAINT CURRENCY_P ENABLE VALIDATE;確認(rèn)主表currency的逐漸約束是否生效ethan_userethanDB> select constraint_name,constraint_type ,status,table_name from user_constraints where constraint_name='CURRENCY_P'CONSTRAINT_NAME-C STATUS- -TABLE_NAME-CURRENCY_PP ENABLEDCURRENCY文章至此?！究偨Y(jié)】1. 本文記錄了主表在有子表外鍵約束的情況，如何快速批量刪除和插入數(shù)據(jù)；2. 約束條件const