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.