How to resolve cascading deletion of multiple tables in Oracle

When creating a database, all foreign keys are not cascade to prevent accidental deletion. I don't know when I entered a batch of wrong data, but I used the software to automatically import the data into the database. if I found something wrong, it brought me endless trouble, it is difficult to delete tables. The relationship between tables is complex, and the data volume is large. deleting data one by one is absolute.

When creating a database, all foreign keys are not cascade to prevent accidental deletion. I don't know when I entered a batch of wrong data, but I used the software to automatically import the data into the database. if I found something wrong, it brought me endless trouble, it is difficult to delete tables. The relationship between tables is complex, and the data volume is large. deleting data one by one is absolute.

When creating a database, all foreign keys are not cascade to prevent accidental deletion. I don't know when I entered a batch of wrong data, but I used the software to automatically import the data into the database. If I clicked a mistake, it would be difficult to delete it.

The relationship between tables is complex, and the data volume is large. Deleting one table by one will definitely cause a big problem. So I tried several solutions, and now I will summarize them.

Method 1: Set grading and delete when creating constraints
(However, cascade deletion is not set during database creation for various reasons or for various reasons)
SQL statement:

The Code is as follows:

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: when creating a constraint, cascade deletion is not used. When you need to use cascade deletion, delete the original foreign key constraint and recreate the constraint with cascade deletion.
(The experiment proves that it is completely feasible. You must verify the existing data. Otherwise, the newly created constraint is not efficient for the original data. By default, it is verified. If it is mandatory, use the NOVALIDATE keyword. In addition, the foreign key reference can only be a unique primary key)
SQL statement:

The Code is as follows:

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, you can delete cascade operations. After deletion, if you are not comfortable with such constraints and are not too troublesome, You can recreate them as key constraints without cascade deletion to prevent misoperation)

Method 3: Use a trigger (the trigger is not deleted at the time of creation)
(Relatively flexible, you can write your own program, reference is not a unique primary key can also be)
(1) create a table and insert data
SQL statement:

The Code is as follows:

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

Result:
(2) create a trigger:
SQL:

The Code is as follows:

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:

The Code is as follows:

Delete from "FG123". "ORDERCOMBINE"
WHERE FormerId = '1'

Method 4: If the relationship between tables is simple (for example, two tables), there are very few records involved (a total of several rows of records), and relevant records in the sub-table are deleted directly, delete the records in the parent table.
(The preceding method is efficient only when the data volume is large and the relationship between tables is complex. It is faster to simply delete the data directly)
In the above example, delete directly
SQL statement:

The Code is as follows:

Delete from "FG123". "ORDERCOMBINE"
WHERE VIPForm = '1 ';

Delete from "FG123". "ORDERCOMBINE"
WHERE FormerId = '1'