Oracle多表级联删除方法

创建数据库时为了防止其他人不小心删除操作错误， 所有的外键都没有加级联删除。哪知，不知什么时候自己入了一批错误的数据进去，入库使用的是软件自动的，一下点错给自己带来无尽麻烦啊，删除就不好办了。 表间的关系比较复杂，数据量又比较多，一个个删绝对会出大问题。于是实验了几种解决的办法，现小结一下。

    方法一：创建约束时设定级联删除 

     

    （但一般由于各种原因或出于各种考虑在创建数据库时没有设定级联删除） 

     

    SQL语句： 

     

    CREATE TABLE "U_WEN_BOOK"."FARTAB" ("FAR_ID" NUMBER(10) NOT NULL, 

     

        "FAR_NAME" VARCHAR2(10), "OTHER_NAME" VARCHAR2(10), 

     

        CONSTRAINT "PK_FER" PRIMARY KEY("FAR_ID")) 

     

    CREATE TABLE "U_WEN_BOOK"."CHILTAB" ("CHIL_ID" NUMBER(10) NOT 

     

        NULL, "CHIL_NAME" VARCHAR2(10), "CHIL_OTHER" VARCHAR2(10), 

     

        "FAR_ID" NUMBER(10) NOT NULL, 

     

        CONSTRAINT "PK_CHIL" PRIMARY KEY("CHIL_ID"), 

     

        CONSTRAINT "FK_CHIL" FOREIGN KEY("FAR_ID") 

     

    REFERENCES "U_WEN_BOOK"."FARTAB"("FAR_ID") ON DELETE CASCADE) 

     

    方法二：创建约束时没有使用级联删除，在需要使用级联删除时，删除原来的外键约束，重建带级联删除的约束 

     

    （实验证明完全可行，注意需要对已经存在的数据进行验证，否则新建的约束对原有数据不具备效率，默认是验证的，若强制要求不验证，使用NOVALIDATE关键词。还有外键引用的只能是唯一主键） 

     

    SQL语句： 

     

    ALTER TABLE "U_WEN_BOOK"."GCHILTAB1" 

     

        DROP CONSTRAINT "FK_G1" 

     

    ALTER TABLE "U_WEN_BOOK"."GCHILTAB1" 

     

      ADD (CONSTRAINT "FK_G1" FOREIGN KEY() 

     

        REFERENCES "U_WEN_BOOK"."CHILTAB"() 

     

        ON DELETE CASCADE) 

     

    （这样就可以级联删除了，删除完后，如果不放心这样的约束条件，并且不嫌麻烦可以再重建为不带级联删除等外键约束，防止误操作）

方法三：使用触发器（创建时没有级联删除） 

     

    （比较灵活，可以根据自己编写的程序进行，引用的不是唯一主键也可以） 

     

    （1）       创建表及插入数据 

     

          SQL语句： 

     

    create   table orderCombine   (  

     

    O_Id                     VARCHAR2(16)     not   null,  

     

    OrderId           VARCHAR2(15)     not   null,                        

     

    FormerId         VARCHAR2(16)     not   null,  

     

    constraint   PK_ORDERCOMBINE   primary   key   (FormerId) ); 

     

       create   table   VIPForm     (  

     

    V_Id                     VARCHAR2(16)       not   null,              

     

    IsValid           CHAR(1)   default   '0'     not   null     ,                  

     

    Constraint fk_vipform foreign key(V_id)   references   ordercombine(formerid) );  

     

    insert into orderCombine values('1','1','1'); 

    insert into orderCombine values('2','2','2'); 

    insert into vipform values('1','5'); 

    insert into vipform values('2','4'); 

    insert into vipform values('1','2'); 

     

    结果： 

    

    （2）创建触发器： 

     

    SQL： 

     

    CREATE OR REPLACE TRIGGER "FG123"."TER_OV" 

     

    BEFORE 

     

    DELETE ON "ORDERCOMBINE" FOR EACH ROW 

     

    BEGIN 

     

    DELETE   FROM   VIPForm 

     

    WHERE   VIPForm.V_Id=:OLD.FormerId; 

     

    END; 

     

    （3）       删除及结果： 

     

    SQL： 

     

    DELETE FROM "FG123"."ORDERCOMBINE" 

     

    WHERE FormerId =’1' 

     

    方法四：若表间关系简单（就两张表），涉及到的记录也很少（总共就几行记录），直接删除子表中的相关记录，再删除父表中的记录即可。 

    

    （前面的方法在涉及数据量较大以及表间关系比较复杂时才有效率上的优势，简单的直接删除来的更快） 

     

    如上例中，直接删除 

     

    SQL语句： 

     

    DELETE FROM "FG123"."ORDERCOMBINE" 

     

    WHERE VIPForm =’1'； 

     

    DELETE FROM "FG123"."ORDERCOMBINE" 

     

    WHERE FormerId =’1'；

 

oracle数据库中表格的级联删除问题

2009-08-29 15:46LG772EF01 | 分类：数据库DB | 浏览9149次

数据库表中没有设置级联删除.怎样用SQL语句实现:
如:EMP表中有字段DEPT_NO是外键
   POS表中有字段DEPT_NO是外键
   DEPT表中有字段DEPT_NO,
如何实现删除DEPT表中数据时将EMP表,POS表中的相关数据也删除;
哪位大侠指点指点

2009-08-29 17:50提问者采纳

两种方法，个人建议你选择方法一，简单方便

方法一：触发器解决（下面的代码可以不用修改，copy直接用）
create or replace trigger delete_dept
before delete on DEPT
for each row
begin
 delete from EMP where DEPT_NO = :old.DEPT_NO;
 delete from POS where DEPT_NO = :old.DEPT_NO;
end;
/

方法二：修改你的外键设置，达到级联删除的目的，具体实现如下：
 a)先查询出EMP表和POS表中 外键的名称(如果你知道 外键名这一步可以省略)
 select CONSTRAINT_NAME,TABLE_NAME from user_constraints where CONSTRAINT_TYPE ='R' and TABLE_NAME in('EMP','POS');
 
 b)删除EMP表和POS表上的外键后 重新建立允许级联删除的外键模式
   alter table EMP drop constraint 外键名;
   alter table POS drop constraint 外键名;
   alter table EMP add constraint 外键名 foreign key(DEPT_NO) references DEPT(DEPT_NO) on delete cascade;
   alter table POS add constraint 外键名 foreign key(DEPT_NO) references DEPT(DEPT_NO) on delete cascade;

---
以上，希望对你有所帮助。