Analysis of Cascade Deletion of Multiple Tables in Oracle

The relationship between tables is complex, and the amount of data is relatively large, so deleting one table will definitely cause big problems. Therefore, several solutions have been experimented, and now the summary is 1.

Method 1: Set cascading deletion when creating constraints
(However, for various reasons or considerations, cascading deletion is not set when creating the database.)
SQL statement:

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) 

Method 2: Create constraints without using cascading deletion. When cascading deletion is needed, delete the original foreign key constraint and rebuild the constraint with cascading deletion
(Experiments prove that it is completely feasible. Pay attention to the need to verify the existing data, otherwise the newly created constraint is inefficient for the original data, and it is verified by default. If it is mandatory not to verify, use NOVALIDATE keywords. There are also foreign keys that can only refer to only 1 primary key)
SQL statement:

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) 
 (In this way, cascade deletion can be made. After deletion, if you don't trust such constraints and don't bother, you can rebuild it without foreign key constraints such as cascade deletion to prevent misoperation.) 

Method 3: Use triggers (create without cascading deletion)
(More flexible, can be prepared according to their own procedures, reference is not only 1 primary key can also)
(1) Create tables and insert data
SQL statement:

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'); 

Results:
(2) Create triggers:
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) Deletion and result:
SQL:

DELETE FROM "FG123"."ORDERCOMBINE" 
WHERE FormerId ='1'

Method 4: If the relationship between tables is simple (just two tables) and there are few records involved (only a few lines of records in total), delete the related records in the child table directly, and then delete the records in the parent table.
(The previous method has the advantage of efficiency when the amount of data involved is large and the relationship between tables is complex, and simple direct deletion is faster)
As in the above example, delete directly
SQL statement:

DELETE FROM "FG123"."ORDERCOMBINE" 
WHERE VIPForm ='1' ; 

DELETE FROM "FG123"."ORDERCOMBINE" 
WHERE FormerId ='1'