Oracle Cascade Delete

Use on DELETE cascade and on delete set NULL in Oracle to establish foreign keys

The surface we describe the creation of a foreign key constraint when using the Oracle default creation method, when deleting the referenced data will not be deleted, which gives us more flexibility in oracle9i, we use the ON DELETE cascade and the on delete set The NULL keyword determines whether the data referenced by this data will be deleted when the referenced data is deleted, or the corresponding value of the data that references the data will be empty.

For example, the basic information of the employee and the company's department information are stored separately in the following two tables. We are
CREATE TABLE Dept
(Deptno number (TEN) is not NULL,
Deptname VARCHAR2 (+) NOT NULL,
Constraint Pk_dept primary KEY (DEPTNO));
And
CREATE TABLE EMP
(empno number (TEN) is not NULL,
fname VARCHAR2 (20),
LName VARCHAR2 (20),
Dept Number (10),
Constraint Pk_emp primary KEY (Empno));

Then we now use these two keywords to increase the foreign key to try, first let's try on the delete cascade

ALTER TABLE EMP
Add constraint fk_emp_dept foreign key (dept) References Dept (deptno) on DELETE cascade;
Increase the foreign key first. Then insert the data.
INSERT INTO Dept values (1, ' Sales Department ');
INSERT INTO Dept values (2, ' finance Department ');
INSERT into EMP values (2, ' Mary ', ' Song ', 1);
INSERT into EMP values (3, ' Linda ', ' Liu ', 2);
INSERT into EMP values (4, ' Linlin ', ' Zhang ', 1);
And now I have to delete the sales department, what will be the consequences?
Delete from dept where deptno = 1;
We found that in addition to a dept in the data is deleted, EMP in the two data is also deleted, where two of the EMP data is referenced by the sales department of this data, it is easy to understand on the delete cascade.

Next we look at the delete set NULL, as the name implies, the foreign KEY constraint established in this way, when the referenced data is deleted, the corresponding value of the data referenced by the data will become null, and the following is a test to prove that the on delete set NULL effect:
First restore the data that you just made, and then change the constraint:
ALTER TABLE EMP
Add constraint fk_emp_dept foreign key (dept) References Dept (deptno) on delete set null;
We then perform the delete operation:
Delete from dept where deptno = 1;
You will also find that in addition to the Sales Department in dept, the value of the Dept of the two data in the EMP that references this data is automatically assigned, which is the function of the on delete set NULL.

Use on delete set NULL one thing to note is that the column referenced by the other table must be able to be empty and not have a NOT NULL constraint, and for the above example the Dept column must not have a NOT NULL constraint, if the NOT NULL constraint is already defined, When you use the on delete set NULL to delete the referenced data, it will occur: ORA-01407: Unable to update ("DD". " EMP "." DEPT ") is a null error.

In general, the effect of on DELETE cascade and on delete set NULL is to handle cascading delete problems, and if you need to delete data that is referenced by other data, then you should decide exactly what you want Oracle to do with those data that are about to be deleted. You can have three different ways:
Prohibit deletion. This is also the Oracle default
Empty the corresponding column of data that references this value, which requires the use of the on delete set NULL keyword
Delete the data that references this value, which requires the use of the ON DELETE cascade keyword

Oracle Cascade Delete (GO)