Oracle FOREIGN KEY CASCADE DELETE and Cascade update

1 Cascade Delete

Oracle Delete on foreign keys has no action (similar to restrict), cascade, and set null three behavior.

Below the student-class as an example to explain the foreign key deletion under different circumstances, students belong to the class, the primary key of the class is the student's foreign key.

--Class Tables Crate table Tb_class (ID number not NULL,--class primary Key name VARCHAR2 (50),--Class name CONSTRAINT Pk_tb_class PRIMARY KEY (ID)); --Student Table CREATE TABLE tb_student (ID number not NULL,--Student primary Key name VARCHAR2 (50),--Student name class_id number ,--student class, foreign key--PRIMARY KEY constraint CONSTRAINT pk_tb_student PRIMARY key (ID),--FOREIGN KEY constraint--set cascade Delete to No ACTION CONSTRAINT F  k_tb_student_class_id FOREIGN KEY (class_id) REFERENCES Tb_class (ID));--Add class data insert into Tb_class (ID, NAME) VALUES (1, ' One shift '); INSERT into Tb_class (ID, name) VALUES (2, ' Class two '); INSERT into Tb_class (ID, Name) VALUES (3, ' class three ');--Add student data INSERT I NTO tb_student (ID, name, class_id) VALUES (1, ' xiaoming ', 1); INSERT into tb_student (ID, name, class_id) VALUES (2, ' small just ', 1); in  SERT into tb_student (ID, name, class_id) VALUES (3, ' Xiao Wang ', 1); INSERT into tb_student (ID, name, class_id) VALUES (4, ' Er Ming ',  2) insert into tb_student (ID, name, class_id) VALUES (5, ' two just ', 2); INSERT into tb_student (ID, name, class_id) VALUES (6, ' Two kings ', 2); INSERTinto Tb_student (ID, name, class_id) VALUES (7, ' Daming ', 3); INSERT into tb_student (ID, name, class_id) VALUES (8, ' Big just ', 3); I Nsert into Tb_student (ID, NAME, class_id) VALUES (9, ' King ', 3);

Initial class data

Initial student Data

1.1 NO ACTION

No action means that when you delete data from a referenced column in a primary table, it is forbidden to execute if the reference column of the child table contains the value.

Now student foreign KEY cascade Delete is no action, perform delete class operation.

--delete The three shifts delete from Tb_class WHERE id=3;

Oracle will prompt for violation of integrity constraints.

If you want to delete class three, you must first delete the three classes of students.

--Delete The three shifts students delete from tb_student where class_id=3;--delete the three shifts delete from Tb_class where id=3;

1.2 SET NULL
Set NULL refers to setting the value of the corresponding reference column in the child table to a null value when the data for the referenced column in the primary table is deleted. Set NULL is a precondition that the foreign key reference column must be set to NULL.

Change the foreign key deletion behavior of the student table (tb_student) to set NULL. Oracle does not seem to have modify constraint operations, it can only delete the foreign key and then create a new.

--Delete foreign key of student table (tb_student) ALTER TABLE tb_student drop CONSTRAINT fk_tb_student_class_id;--Delete add on delete SET null foreign key alter TABLE tb_student ADD CONSTRAINT fk_tb_student_class_id FOREIGN KEY (class_id) REFERENCES Tb_class (ID) on DELETE SET NULL; --Delete a shift delete from Tb_class WHERE id=1;

Because the on delete of the foreign key is set NULL, the class_id of a class of students is set to NULL when the class is deleted.

1.3 CASCADE

Cascade is when you delete data from a referenced column in a primary table, cascade deletes the corresponding data row in the child table.

Change the foreign key deletion behavior of the student table (tb_student) to Cascade.

--Remove the no action foreign key on the Tb_student table ALTER TABLE tb_student drop CONSTRAINT fk_tb_student_class_id;--Delete add on delete Cascade FOREIGN key Alter TABLE tb_student ADD CONSTRAINT fk_tb_student_class_id FOREIGN KEY (class_id) REFERENCES Tb_class (ID) on D ELETE cascade;--Delete the second shift delete from Tb_class WHERE id=2;

Because the foreign key's on delete is Cascade, when the second class is deleted, the students in the second class are also deleted.

2 cascading updates

Oracle does not natively support cascading updates for foreign keys, but you can achieve the effect of cascading updates in the following ways.

The first step is to understand the Oracle delay and non-delay constraints. The non-delay constraint is the view of the constraint as soon as the record is modified, and the modification cannot be performed because some constraints are violated. Delay constraints are not checked at the time of the first modification, but only when they are submitted. Oracle's cascading updates are implemented using this feature.

Oracle's foreign key default is a non-delay constraint, and modifying a student's foreign key is a delay constraint.

--Delete existing foreign key on student table (tb_student) ALTER TABLE tb_student DROP CONSTRAINT fk_tb_student_class_id;--add delay constraint foreign key ALTER TABLE tb_ STUDENT ADD CONSTRAINT fk_tb_student_class_id FOREIGN KEY (class_id) REFERENCES Tb_class (ID) on DELETE CASCADE deferrable ;

Set the trigger to update the foreign key (class_id) of the Student table (tb_student) When the class table (Tb_class) 's primary key changes.

CREATE OR REPLACE TRIGGER tgr_tb_class_update after UPDATE of the ID on Tb_classfor each rowbegin  if:old.id<>:new.i D then    UPDATE tb_student SET class_id=:new.id WHERE class_id=:old.id;  END IF; END;

Note:

The Oracle FOREIGN KEY Cascade Update method can be used for foreign and foreign key reference primary keys on different tables. However, it is often the case that when you save hierarchical data in a database, the foreign key of the table references the primary key of the same table. Cascading updates cannot be implemented with triggers at this time.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle FOREIGN KEY CASCADE DELETE and Cascade update