Constraints (Part 6) - On Delete Cascade

Go to part 5

There is more than one way to create a foreign key constraint. This example, tested on an Oracle 9 database, uses the on delete cascade clause. First create a department table:  

SQL> create table dept

  2  (dept_no  varchar2(4) unique,

  3  dept_desc varchar2(10))

  4  /

Table created.

SQL>

Next create an employee table using the on delete cascade clause. We will see what this is for later:

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 cascade)

  8  /

Table created.

SQL> 

Set up an IT department with one employee: 

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

  2  /

1 row created.

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

  2  /

1 row created.

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>

Now delete the IT department from the dept table. The on delete cascade clause means that any employee(s) in this department will be deleted from the emp table without warning:

SQL> delete dept

  2  /

1 row deleted.

SQL> select * from dept

  2  /

no rows selected

SQL> select * from emp

  2  /

no rows selected

SQL>

Later on, i.e. when it is too late to rollback the transaction, you may decide that the IT department was removed in error. If you then reinstate it in the dept table, you will somehow need to find the names of the employees who work there and add them to the emp table again.