Oracle foreign key cascade deletion and cascade update, oracle key level update

Oracle foreign key cascade deletion and cascade update, oracle key level update

Cascading Deletion

Oracle has three behaviors: no action (similar to RESTRICT), CASCADE, and set null.

The following uses the student-class as an example to describe how to delete Foreign keys in different situations. A student belongs to a class and the primary key of the class is the foreign key of the student.

-- Class table 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 PK_TB_STUDENT primary key (ID), -- foreign key constraint -- set cascade deletion to no action constraint FK_TB_STUDENT_CLASS_ID foreign key (CLASS_ID) REFERENCES TB_CLASS (ID )); -- add the class data insert into TB_CLASS (ID, NAME) VALUES (1, 'first class'); insert into TB_CLASS (ID, NAME) VALUES (2, 'second class '); insert into TB_CLASS (ID, NAME) VALUES (3, 'three class'); -- add student data insert into TB_STUDENT (ID, NAME, CLASS_ID) VALUES (1, 'xiaoming ', 1); insert into TB_STUDENT (ID, NAME, CLASS_ID) VALUES (2, 'xiaogang ', 1); insert into TB_STUDENT (ID, NAME, CLASS_ID) VALUES (3, 'Little king', 1); insert into TB_STUDENT (ID, NAME, CLASS_ID) VALUES (4, 'two', 2); insert into TB_STUDENT (ID, NAME, CLASS_ID) VALUES (5, 'digang', 2); insert into TB_STUDENT (ID, NAME, CLASS_ID) VALUES (6, 'second king', 2); insert into TB_STUDENT (ID, NAME, CLASS_ID) VALUES (7, 'daming ', 3); insert into TB_STUDENT (ID, NAME, CLASS_ID) VALUES (8, 'dagang', 3 ); insert into TB_STUDENT (ID, NAME, CLASS_ID) VALUES (9, 'dawang ', 3 );

Initial class data

Initial student data

1.1 NO ACTION

No action indicates that when you delete data from referenced columns in a primary table, this operation is prohibited if the referenced column in The subtable contains this value.

Currently, the student's external key cascade deletion operation is no action, and the class deletion operation is performed.

-- DELETE three shifts delete from TB_CLASS where id = 3;

Oracle will prompt a violation of integrity constraints ,.

If you want to delete three students, you must first Delete the three students.

-- DELETE three-class student delete from TB_STUDENT WHERE CLASS_ID = 3; -- DELETE three-class delete from TB_CLASS where id = 3;

1.2 SET NULL
Set null indicates that when the data of referenced columns in the primary table is deleted, the value of the referenced column in The subtable is SET to NULL. The premise of set null is 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 the modify constraint operation. You can only delete the foreign key and then create a new one.

-- DELETE the foreign key alter table TB_STUDENT drop constraint foreign; -- 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 class delete from TB_CLASS where id = 1;

Because the on delete function of the foreign key is set null, when the first class is deleted, the CLASS_ID of the first class is SET to NULL ,.

1.3 CASCADE

CASCADE refers to the CASCADE deletion of corresponding data rows in the subtable when the data of referenced columns in the primary table is deleted.

Change the foreign key deletion behavior of the student table (TB_STUDENT) to CASCADE.

-- DELETE the no action foreign key alter table TB_STUDENT drop constraint foreign; -- DELETE the add on delete cascade foreign key alter table TB_STUDENT add constraint foreign key (CLASS_ID) REFERENCES TB_CLASS (ID) on delete cascade; -- DELETE the second-class delete from TB_CLASS where id = 2;

Because the on delete function of the foreign key is CASCADE, when the second class is deleted, the students in the second class are also deleted.

2 cascade updates

Oracle does not support cascade updates of Foreign keys, but you can perform cascade updates as follows.

First, you must understand the Oracle latency constraints and non-latency constraints. The non-delay constraint is to check the constraints immediately when you modify the record. It indicates whether the modification cannot be performed because it violates certain constraints. The latency constraint is not checked when the modification is made. It is checked only when the submission is made. This feature is used for Oracle cascade updates.

Oracle Foreign keys are non-latency constraints by default, and students' foreign keys are modified as latency constraints.

-- Delete the existing foreign key alter table TB_STUDENT drop constraint on the student TABLE (TB_STUDENT); -- ADD the foreign key alter table TB_STUDENT add constraint into foreign key (CLASS_ID) REFERENCES TB_CLASS (ID) on delete cascade deferrable;

Set the trigger. When the primary key of the class table (TB_CLASS) changes, the foreign key (CLASS_ID) of the student table (TB_STUDENT) is updated ).

CREATE OR REPLACE TRIGGER TGR_TB_CLASS_UPDATE AFTER UPDATE OF ID ON TB_CLASSFOR EACH ROWBEGIN  IF :OLD.ID<>:NEW.ID 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 the foreign key and foreign key reference primary keys in different tables. However, when you save hierarchical data in a database, the foreign key of the table references the primary key of the same table. At this time, the trigger cannot be used for Cascade update.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.