Oracle Series-Cascade DELETE and cascade updates

must state : This blog is reproduced in Oracle foreign KEY cascade DELETE and cascade update http://www.2cto.com/database/201507/417496.html

In view of the previous collection of wonderful blog has been deleted, it is very sad, so still want to copy

First, 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.

1 --Class Table2CRATETABLETb_class3 (4Id Number  not NULL,--Class primary Key5NAMEVARCHAR2( -),--class name6   CONSTRAINTPk_tb_classPRIMARY KEY(ID)7 );8  9 --Student TableTen CREATE TABLEtb_student One ( AId Number  not NULL,--Student PRIMARY Key -NAMEVARCHAR2( -),--Student Name -class_id Number,--students belong to class, foreign key the     -   --PRIMARY KEY constraint -   CONSTRAINTPk_tb_studentPRIMARY KEY(ID), -     +   --FOREIGN KEY Constraints -   --set Cascade Delete to No ACTION +   CONSTRAINTfk_tb_student_class_idFOREIGN KEY(class_id)REFERENCESTb_class (ID) A ); at   - --Add Class Data - INSERT  intoTb_class (ID, NAME)VALUES(1,'Class One'); - INSERT  intoTb_class (ID, NAME)VALUES(2,'Class Two'); - INSERT  intoTb_class (ID, NAME)VALUES(3,'class Three'); -   in --Add student Data - INSERT  intoTb_student (ID, NAME, class_id)VALUES(1,'Xiao Ming',1); to INSERT  intoTb_student (ID, NAME, class_id)VALUES(2,'Xiao Gang',1); + INSERT  intoTb_student (ID, NAME, class_id)VALUES(3,'Xiao Wang',1); - INSERT  intoTb_student (ID, NAME, class_id)VALUES(4,'two Ming',2); the INSERT  intoTb_student (ID, NAME, class_id)VALUES(5,'two just',2); * INSERT  intoTb_student (ID, NAME, class_id)VALUES(6,'Two kings',2); $ INSERT  intoTb_student (ID, NAME, class_id)VALUES(7,' Daming',3);Panax Notoginseng INSERT  intoTb_student (ID, NAME, class_id)VALUES(8,'da Gang',3); - INSERT  intoTb_student (ID, NAME, class_id)VALUES(9,'King',3);

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.

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.

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.

Second, cascade Update

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.

1 --Delete an existing foreign key on the student table (tb_student)2 ALTER TABLETb_studentDROP CONSTRAINTfk_tb_student_class_id;3 --Add a Delay constraint foreign key4 ALTER TABLETb_studentADD CONSTRAINTfk_tb_student_class_idFOREIGN KEY(class_id)REFERENCESTb_class (ID) on DELETE CASCADEdeferrable;

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.

1 CREATE OR REPLACE TRIGGERtgr_tb_class_update2AfterUPDATE  ofId onTb_class3  forEach ROW4 BEGIN5   IF: old.id<>: new.id Then6     UPDATETb_studentSETclass_id=: new.idWHEREclass_id=: old.id;7   END IF;8 END;

Oracle Series-Cascade DELETE and cascade updates