A tutorial on cascading queries, cascading deletes, and cascading updates in Oracle databases _oracle

cascading queries
there is a way to implement cascading queries in Oracle databases

SELECT *    //fields from table///To query    with the child contact ID and the parent Contact ID's Table 
start with selfid=id  ///Given a startid (field name is the child contact ID, and the starting ID number)
connect by prior Selfid=parentid  //Join condition is a child point equal to the parent contact and cannot be reversed

This SQL is used primarily for cascading queries of menus, and for a parent contact to detect all the child contacts. and the child contact of the child contact, a check in the end, very practical. However, this program can only be used in Oracle, I do not yet know how to call in other databases. When I find out, and then posted out to share with you.
This procedure, it is estimated that a lot of people do not understand, in fact, put so long I also temporarily did not see understand, again measured a bit, added to explain, or I can not understand the next time.
Take the menu of a Windows system as an example. My one of these table menu.
Description
Mid: The ID number of the menu
Mname: Menu Name
Mpid: Menu of
Quickey: Shortcut keys
Validate: Permission table (for userid, or role ID)

If I want to know if I have those submenus under the File menu. I can use this SQL program like this:

SELECT * from menu
start and mid=1  
connect by prior mid=mpid;

This allows you to list all the submenus in the file. Of course, the actual application will not be so simple, such as the additional conditions, especially the rights management, at this time according to your system requirements, is the verification of all, or the role of verification, the ID of these people put in validate this field, composed of a string, n IDs separated by commas, (note that When you save to the database, pay attention to the string processing, intercept the last comma so you can save a lot of trouble.

SELECT * FROM menu
where validate in (...)
and mid in (
 the Select Mid from menu///no * number is available here.)
 start with mid=1  
 connect by prior mid=mpid;
)

Finally, add a little bit more about random query code

SELECT * from User order by Sys_guid ()

cascading deletes

Oracle has no action (similar to restrict), cascade, and set NULL three behavior on deletion of foreign keys.
Below the student-class as an example to explain the different cases of foreign key deletion, students belong to the class, the primary key of the class is the student's foreign key.
--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 ()-  student name 
 class_id Number,--   student-owned class, foreign key 
 
 --PRIMARY KEY constraint 
 CONSTRAINT pk_tb_student PRIMARY Key (ID), 
 
 --FOREIGN KEY constraint- 
 -set cascade Delete to No ACTION 
 CONSTRAINT fk_tb_student_class_id FOREIGN KEY (class_id) REFERENCES tb_class (ID) 
; 

--Adding class data

INSERT into Tb_class (ID, NAME) VALUES (1, ' class '); 
INSERT into Tb_class (ID, NAME) VALUES (2, ' Class Two '); 
INSERT into Tb_class (ID, NAME) VALUES (3, ' class three '); 

--Adding student data

INSERT into Tb_student (ID, NAME, class_id) VALUES (1, ' xiaoming ', 1); 
INSERT into Tb_student (ID, NAME, class_id) VALUES (2, ' Xiao Gang ', 1); 
INSERT 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); 
INSERT into Tb_student (ID, NAME, class_id) VALUES (7, ' Daming ', 3); 
INSERT into Tb_student (ID, NAME, class_id) VALUES (8, ' da Gang ', 3); 
INSERT into Tb_student (ID, NAME, class_id) VALUES (9, ' King ', 3); 

Initial class data

Initial student Data

NO ACTION:

No action means that when you delete data from a referenced column in a primary table, the action is disabled if the reference column of the child table contains the value.
Now the student foreign key cascade deletion is no action, performing the delete class operation.

--delete class three

DELETE from Tb_class WHERE id=3; 

Oracle prompts for a violation of the integrity constraint, as shown in the figure.

If you want to delete class three, you must first remove the students in class three.
--Delete three classes of students

DELETE from Tb_student WHERE class_id=3; 

--delete class three

DELETE from Tb_class WHERE id=3; 

SET NULL:

Set NULL means that the value of the corresponding reference column in the child table is set to a null value when the data for the referenced column in the primary table is deleted. Set NULL has 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, you can delete the foreign key first and then create a new.

--Remove the foreign key from the student table (tb_student) Table

ALTER TABLE tb_student DROP CONSTRAINT fk_tb_student_class_id; 

--delete Add on delete SET null foreign key

Copy Code code 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 the class is deleted, the class_id of the class of students is set to NULL, as shown in the figure.

CASCADE

Cascade refers to the cascade deletion of the corresponding data row in the child table when data is deleted from the referenced column in the primary table.
Change the foreign key deletion behavior of the student table (tb_student) to Cascade.

--Delete 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 DELETE CAS CADE; 

--delete Class two

DELETE from Tb_class WHERE id=2; 

Because the on delete of the foreign key is cascade, the students under Class two will also be deleted when class two is deleted.

cascading Updates

Oracle itself does not support cascading updates of foreign keys, but the effect of cascading updates can be achieved in the following ways.
First you should understand Oracle latency constraints and non-delay constraints. A non-deferred constraint is a view of the constraint immediately when the record is modified, and whether the modification cannot be performed because some constraints have been violated. Deferred constraints are not checked at the time of the modification, only when they are submitted. Oracle cascade updates are implemented using this feature.
Oracle's foreign key defaults to non-delay constraints, and the student's foreign key is modified as a deferred constraint.

--Delete an existing foreign key on the student table (tb_student)

ALTER TABLE tb_student DROP CONSTRAINT fk_tb_student_class_id; 

--Adding a 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 CAS CADE deferrable; 

Sets the trigger, and updates the foreign key (class_id) of the Student table (tb_student) when the primary key of the class table (Tb_class) changes.

CREATE OR REPLACE TRIGGER tgr_tb_class_update after UPDATE's 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; 

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