When creating foreign key constraints using Oracle's default method, referenced data cannot be deleted if there are dependent rows. However, in Oracle9i, we have more flexible options. We can use the `ON DELETE CASCADE` and `ON DELETE SET NULL` keywords to decide whether to delete all the data that references the referenced data or set the corresponding values of the referencing data to NULL when the referenced data is deleted.
For example, consider the following two tables that store employee information and department information within a company:
```sql
CREATE TABLE dept (
deptno NUMBER(10) NOT NULL,
deptname VARCHAR2(30) NOT NULL,
CONSTRAINT pk_dept PRIMARY KEY (deptno)
);
CREATE TABLE emp (
empno NUMBER(10) NOT NULL,
fname VARCHAR2(20),
lname VARCHAR2(20),
dept NUMBER(10),
CONSTRAINT pk_emp PRIMARY KEY (empno)
);
```
Now let's try adding foreign keys using these two keywords, starting with `ON DELETE CASCADE`.
```sql
ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept) REFERENCES dept(deptno) ON DELETE CASCADE;
```
First, add the foreign key, then insert some data:
```sql
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);
```
Now, what happens if we delete the Sales Department?
```sql
DELETE FROM dept WHERE deptno = 1;
```
We find that not only is one row from the `dept` table deleted, but also two rows from the `emp` table are deleted. These two rows in the `emp` table reference the Sales Department data. This makes it easy to understand the meaning of `ON DELETE CASCADE`.
Next, let's look at `ON DELETE SET NULL`. As its name suggests, when this type of foreign key constraint is used, if the referenced data is deleted, the corresponding values of the referencing data will be set to NULL. Let's demonstrate the effect of `ON DELETE SET NULL` through an experiment.
First, restore the original data and modify the constraint:
```sql
ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept) REFERENCES dept(deptno) ON DELETE SET NULL;
```
Then execute the deletion operation again:
```sql
DELETE FROM dept WHERE deptno = 1;
```
You'll notice that apart from the Sales Department being deleted from the `dept` table, the `dept` column values for the two rows in the `emp` table that referenced this data are automatically set to NULL. This is the effect of `ON DELETE SET NULL`.
One important note about using `ON DELETE SET NULL`: the column in the referencing table must allow NULL values; it cannot have a `NOT NULL` constraint. For the above example, the `dept` column in the `emp` table must not have a `NOT NULL` constraint. If a `NOT NULL` constraint has already been defined and you attempt to delete referenced data using `ON DELETE SET NULL`, the following error will occur: `ORA-01407: Cannot update ("DD"."EMP"."DEPT") to NULL`.
In summary, both `ON DELETE CASCADE` and `ON DELETE SET NULL` are used to handle cascading deletion issues. If you need to delete data that is referenced by other data, you need to decide how Oracle should handle the data that references the soon-to-be-deleted data. You have three options:
1. Prohibit deletion. This is Oracle's default behavior.
2. Set the corresponding columns of the referencing data to NULL. This requires the `ON DELETE SET NULL` keyword.
3. Delete all the referencing data as well. This requires the `ON DELETE CASCADE` keyword.
**Article Source**: [Eden Network](http://www.edenw.com/tech/devdeloper/database/2010-07-18/4787.html)