The relationship between the tables is more complex, the amount of data is much more, delete absolutely will be a big problem. So we experimented with several solutions, and now we have a summary.
method One: Set cascading deletes when creating constraints
(But there is no cascading deletion at the time of creation of the database for a variety of reasons or due to various considerations)
SQL statement:
Copy Code code as follows:
CREATE TABLE "U_wen_book". Fartab "(" far_id "number () not NULL,
"Far_name" VARCHAR2 (Ten), "Other_name" VARCHAR2 (10),
CONSTRAINT "Pk_fer" PRIMARY KEY ("far_id"))
CREATE TABLE "U_wen_book". Chiltab "(" chil_id "number () not
NULL, "Chil_name" VARCHAR2 (Ten), "Chil_other" VARCHAR2 (10),
' far_id ' number is 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 Two: Do not use cascading deletes when creating constraints, delete the original foreign KEY constraint when you need to use cascading deletion, and rebuild the constraint with cascading deletion
(experiments prove to be completely feasible, pay attention to the need to verify the existing data, otherwise the new constraint on the original data is not efficient, the default is validated, if the mandatory requirements do not verify, the use of novalidate keywords.) There are also foreign key references that can only be unique primary keys)
SQL statement:
Copy Code code 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)
(This can be cascading delete, after deletion, if you do not trust such constraints, and not too troublesome can be rebuilt without cascading deletion of external key constraints, prevent misoperation)
method Three: Use triggers (no cascade deletions when created)
(more flexible, can be written according to their own program, the reference is not a unique primary key can also)
(1) Creating tables and inserting data
SQL statement:
Copy Code code as follows:
CREATE TABLE Ordercombine (
O_ID VARCHAR2 () NOT NULL,
OrderId VARCHAR2 () NOT NULL,
Formerid VARCHAR2 () NOT NULL,
Constraint Pk_ordercombine primary KEY (Formerid));
CREATE TABLE Vipform (
v_id VARCHAR2 () 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:
Copy Code code 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) Delete and result:
SQL:
Copy Code code as follows:
DELETE from "FG123". " Ordercombine "
WHERE Formerid = ' 1 '
method Four: If the relationship between the tables is simple (in two tables), the records involved are very small (a total of several lines of records), directly delete the related records in the child table, and then delete the record in the parent table.
(The previous approach has the advantage of being more efficient when it comes to large amounts of data and the complexity of the relationships between tables, and simple direct deletion comes faster)
As in the example above, delete directly
SQL statement:
Copy Code code as follows:
DELETE from "FG123". " Ordercombine "
WHERE vipform = ' 1 ';
DELETE from "FG123". " Ordercombine "
WHERE Formerid = ' 1 '
