Summary of statements for cascading deletion of multiple tables in Oracle

In Oracle, the multi-Table cascading deletion method is often used in the case of large data volumes. If we delete small databases, we can directly use delete to delete them, if the data size is large, cascading deletion of multiple tables will reflect the advantages.

Simplest and fastest Deletion

The following describes the efficiency advantage of Multi-Table cascading deletion when the data volume is large and the relationship between tables is complex. Simple direct deletion is faster.

In the above example, delete directly

The Code is as follows:	Copy code
Delete from "FG123". "ORDERCOMBINE" WHERE VIPForm = '1 '; Delete from "FG123". "ORDERCOMBINE" WHERE FormerId = '1 ';

Method 1: Set grading and delete when creating constraints

Format of Oracle cascading deletion:

The Code is as follows:

Copy code

Create table A001 (id int primay key, NAME VARCHAR2 (20 )) Create table A002 (id int references A001 (ID) on delete cascade, age number (2, 0 )) Create table groups ( Id VARCHAR2 (16) CONSTRAINT pk_groupid primary key, Name VARCHAR2 (32 ), Description VARCHAR2 (50) ) TABLESPACE userspace; Create table usringrp ( Group_id VARCHAR2 (16) CONSTRAINT fk_uing_grpid REFERENCES groups (id) On delete cascade, User_id VARCHAR2 (16) ) TABLESPACE userspace; PowerDesigner

Integrity constraints

Restrict ). Modification or deletion operations are not allowed. If the primary key of the primary table is modified or deleted, if the Sub-table contains a sub-record, an error message is displayed. This is the default integrity settings.
Set Null ). If the Oracle foreign key column is allowed to be empty, if you modify or delete the primary key of the master table, set the foreign key column referenced in the child table to NULL ).
Set Default ). If the Default value is specified, if you modify or delete the primary key of the primary table, set the Oracle foreign key referenced in the subtable to the Default value ).
Cascade ). When the primary key of the primary table is changed to a new value, the Oracle foreign key value of the subtable is modified accordingly; or when the primary key record of the primary table is deleted, delete the records of Foreign keys in the sub-table.

Example

The Code is as follows:

Copy code

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:	Copy code
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)