|
|
| Re: Cascade delete [message #8358 is a reply to message #8356] |
Tue, 12 August 2003 21:32  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you include ON DELETE CASCADE in the creation of the referntial integrity constraint, then when any rows in the parent/master table are deleted, all corresponding rows in the child/detail table are deleted. Here is an excerpt from Oracle on-line documentation that provides an example with explanation:
ON DELETE Example
This statement creates the dept_20 table, defines and enables two referential integrity constraints, and uses the ON DELETE clause:
CREATE TABLE dept_20
(employee_id NUMBER(4) PRIMARY KEY,
last_name VARCHAR2(10),
job_id VARCHAR2(9),
manager_id NUMBER(4) CONSTRAINT fk_mgr
REFERENCES employees ON DELETE SET NULL,
hire_date DATE,
salary NUMBER(7,2),
commission_pct NUMBER(7,2),
department_id NUMBER(2) CONSTRAINT fk_deptno
REFERENCES departments(department_id)
ON DELETE CASCADE );
Because of the first ON DELETE clause, if manager number 2332 is deleted from the employees table, then Oracle sets to null the value of manager_id for all employees in the dept_20 table who previously had manager 2332.
Because of the second ON DELETE clause, Oracle cascades any deletion of a department_id value in the departments table to the department_id values of its dependent rows of the dept_20 table. For example, if Department 20 is deleted from the departments table, then Oracle deletes all of that department's employees from the dept_20 table
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
