Operation tutorial of cascade query, cascade deletion and cascade update in Oracle Database

Time:2020-11-21

Hierarchical Queries
In Oracle database, there is a method to realize cascade query

Select * // the field to query
From table // table with child pin ID and parent pin ID 
Start with selfid = ID // give a startid (the field name is the child pin ID, and the start ID number)
Connect by prior self = parentid // the connection condition is that the child node is equal to the parent node, and cannot be reversed

This SQL is mainly used for cascading query of menus. Giving a parent node can find out all the child nodes. And sub contact sub contact, a check to the end, very practical. However, this program can only be used in Oracle. I don’t know how to call it in other databases. When I find it, I will post it to share with you.
It is estimated that many people can’t understand this program. In fact, I didn’t understand it after playing it for so long. I retested it again and explained it again. Otherwise, I can’t understand it next time.
Take a menu of windows system as an example. My watch menu like this.
explain:
Mid: the ID number of the menu
Mname: menu name
MPID: menu
Quick: shortcut key
Validate: permission table (storing userid or role ID)

2016521174602925.png (470×233)

If I want to know which submenus are available under the file menu. I can use this SQL program like this:


select * from menu
start with mid=1  
connect by prior mid=mpid;

In this way, you can list all the submenu in “file”. Of course, the actual application will not be so simple, such as attaching actual conditions, especially permission management. At this time, according to your system requirements, whether to verify each or role, put the ID of these people in the field of validate to form a string, and N IDs are separated by commas. (note that when saving to the database, you should pay attention to the string processing, intercepting the last A comma can save a lot of trouble)

select * from menu
where validate in(……)
and mid in(
 Select mid from menu // you can't use * here.
 start with mid=1  
 connect by prior mid=mpid;
)

Finally, add a little bit about random query code


select * from user order by sys_guid()

cascading deletion

Oracle has no action (similar to restrict), cascade and set null in the deletion of foreign keys.
Take student class as an example to illustrate the foreign key deletion in different situations. The student belongs to the 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 list

CREATE TABLE TB_STUDENT 
( 
 ID number not null, -- student primary key 
 Name VARCHAR2 (50), -- student name 
 CLASS_ ID number, -- the class the student belongs to, foreign key 
 
 --Primary key constraint 
 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 class data

INSERT INTO TB_ Class (ID, name) values (1, 'class one'); 
INSERT INTO TB_ Class (ID, name) values (2, 'class two'); 
INSERT INTO TB_ Class (ID, name) values (3, 'class three');

 
–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, 'Xiaowang', 1); 
INSERT INTO TB_ STUDENT (ID, NAME, CLASS_ ID) values (4, 'Erming', 2); 
INSERT INTO TB_ STUDENT (ID, NAME, CLASS_ ID) values (5, 'Ergang', 2); 
INSERT INTO TB_ STUDENT (ID, NAME, CLASS_ ID) values (6, 'Erwang', 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, 'King', 3);

Initial class data

2016521174720542.png (282×200)

Initial student data

2016521174737420.png (335×302)

NO ACTION:

No action means that when the data of the referenced column in the primary table is deleted, if the reference column of the sub table contains the value, the operation will be prohibited.
Now the cascade deletion of the foreign key of students is no action. Delete the class operation.

–Delete class 3


DELETE FROM TB_CLASS WHERE ID=3; 

Oracle prompts for integrity violations, as shown.

2016521175328858.png (592×262)

If you want to delete class 3, you must first delete the students in class 3.
–Delete class 3 students


DELETE FROM TB_STUDENT WHERE CLASS_ID=3; 

–Delete class 3


DELETE FROM TB_CLASS WHERE ID=3; 

SET NULL:

Set null means that when the data of the referenced column in the main table is deleted, the value of the corresponding reference column in the sub table is set to the null value. The premise of set null is that the foreign key reference column must be set to null.
Put the student list (TB_ The foreign key deletion behavior of student is changed to set null. It seems that Oracle has no modify constraint operation. It can only delete the foreign key first and then create a new one.

–Delete student table (TB_ The foreign key of the student table


ALTER TABLE TB_STUDENT DROP CONSTRAINT FK_TB_STUDENT_CLASS_ID; 

–Delete add on delete set null foreign key

 

Copy codeThe code is as follows:
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 of the foreign key is set null, when deleting a class, the class of the first class will be deleted_ The ID is set to null, as shown.

2016521175351566.png (397×330)

CASCADE

Cascade means that when the data of the referenced column in the main table is deleted, the corresponding data rows in the sub table are cascaded.
Put the student list (TB_ The foreign key deletion behavior of student is changed to cascade.

–Remove TB_ No action foreign key on 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 DELETE CASCADE; 

 
–Delete class 2


DELETE FROM TB_CLASS WHERE ID=2; 

Since the on delete of foreign key is cascade, when class 2 is deleted, the students under class 2 will also be deleted.

update cascade

Oracle does not support cascading updates of foreign keys, but the following methods can be used to achieve the effect of cascading updates.
The first step is to understand Oracle delay and non latency constraints. Non delay constraint is to check whether the modification cannot be performed because some constraints are violated when the record is modified. Delay constraints are not checked when they are first modified, only when they are committed. This feature is used to implement the cascade update of Oracle.
The default foreign key of Oracle is non delay constraint, and the foreign key of students is modified to delay constraint.

–Delete student table (TB_ Existing foreign key on 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 trigger when class table (TB_ If the primary key of class is changed, the student table (TB) will be updated_ Foreign key (class) of student_ ID)。


CREATE OR REPLACE TRIGGER TGR_TB_CLASS_UPDATE 
AFTER UPDATE OF ID ON TB_CLASS 
FOR EACH ROW 
BEGIN 
 IF :OLD.ID<>:NEW.ID THEN 
 UPDATE TB_STUDENT SET CLASS_ID=:NEW.ID WHERE CLASS_ID=:OLD.ID; 
 END IF; 
END; 

be careful:
Oracle foreign key cascade update method can be used for foreign key and primary key referenced by foreign key on different tables. However, we often encounter the following situations: when saving hierarchical data in the database, the foreign key of the table refers to the primary key of the same table. At this time, the cascade update cannot be implemented with triggers.

Recommended Today

Event mechanism principle and usage analysis of Yii framework component

This paper illustrates the principle and usage of event mechanism of Yii framework component. The details are as follows: Before deeply analyzing the operation of Yii, let’s take a look at a very important mechanism event in the framework of Yii. Explanation of component events in Yii official reference document: ======================================================================= Component events are special […]