Oracle multi-Table cascade deletion method

When creating a database using the Oracle multi-Table cascading deletion method, all foreign keys are not cascade to prevent accidental deletion errors. 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. Www.2cto.com Method 1: when creating a constraint, set cascade deletion (but generally, for various reasons or for various reasons, the cascade deletion is not set during database creation) 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_CHI L "primary key (" CHIL_ID "), CONSTRAINT" FK_CHIL "foreign key (" FAR_ID ") REFERENCES" U_WEN_BOOK ". "FARTAB" ("FAR_ID") on delete cascade) Method 2: CASCADE deletion is not used when a constraint is created. When CASCADE deletion is required, the original foreign key constraint is deleted, rebuild the constraint with cascading deletion (The experiment proves that it is completely feasible. Pay attention to the need to verify the existing data. Otherwise, the newly created constraint is not efficient for the original data and is verified by default, if no verification is required, use the NOVALIDATE keyword. A foreign key can only reference a unique 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) (This allows cascading deletion. After deletion, if such constraints are not satisfied, in addition, you can re-create a trigger without any key constraints such as cascade deletion to prevent misoperation. Method 3: Use a trigger (no Cascade deletion is performed at the time of creation) (more flexible, it can be performed according to your own program, and it can be referenced instead of a unique primary key) (1) create a table and insert data SQL statement: create table orderCombine (O_Id VARCHAR2 (16) not n Ull, 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: www.2cto.com (2) CREATE a TRIGGER: 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 (for example, two tables), there are very few records involved (a total of several rows of records ), directly Delete related records in the sub-table and then delete The record in the parent table. Www.2cto.com (the preceding method is more efficient when it involves a large amount of data and the relationship between tables is more complex, and can be simply and directly deleted faster) in the preceding example, delete the SQL statement directly: delete from "FG123 ". "ORDERCOMBINE" WHERE VIPForm = '1'; delete from "FG123 ". "ORDERCOMBINE" WHERE FormerId = '1 ';