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.
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.
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货