Constraints (Part 7) - On Delete Set Null

This example was tested on Oracle 9. It creates another foreign key constraint. First create emp and dept tables as before. Note the on delete set null clause when the employee table is created. We will see what it does later: 

SQL> create table dept
  2  (dept_no  varchar2(4) unique,
  3  dept_desc varchar2(10))
  4  /

Table created.

SQL> create table emp
  2  (emp_no     varchar2(4),
  3  emp_name    varchar2(10),
  4  emp_dept_no varchar2(4)
  5  constraint fk_dept_no
  6  references dept(dept_no)
  7  on delete set null)
  8  /

Table created.

SQL>

And create a department with one employee as in earlier examples: 

SQL> insert into dept values ('D001','IT')
  2  /

1 row created.

SQL> insert into emp values ('E001','Andrew','D001')
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew     D001

SQL> 

Delete the IT department and see what happens to its one employee:

SQL> delete dept
  2  /

1 row deleted.

SQL> select * from dept
  2  /

no rows selected

SQL> 

The employee is still in the emp table but his department number has been set to null. As with the on delete cascade clause in the previous example, you need to be really sure that this is what you want to do:

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew

SQL>

Rollback the transaction, set the employee's department number to be not null, then try again:

SQL> rollback;

Rollback complete.

SQL> alter table emp modify emp_dept_no not null
  2  /

Table altered.

SQL>

As you might expect, the delete dept statement fails because the employee's department number can no longer be set to null:

SQL> delete dept
  2  /
delete dept
      *
ERROR at line 1:
ORA-01407: cannot update
("ORACLE"."EMP"."EMP_DEPT_NO") to NULL

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew     D001

SQL>