6.7 How do I…Add cascading delete functionality?

Problem

I get an error when my application tries to delete data from parent tables, as records in the parent table are referenced by records from other child tables. I know that when I use referential integrity constraints, I need to have a cascade delete functionality to automatically delete all dependent records from the child table, whenever I delete referenced records from the parent table. How do I add a cascading delete functionality?

Technique

The ON DELETE CASCADE is an important feature of the foreign key constraint. The ON DELETE CASCADE can be optionally specified in the REFERENCES clause while defining a foreign key constraint. If it is not specified, a parent table record cannot be deleted until all referencing child table records are deleted first. The ON DELETE CASCADE option allows deletions of parent table rows, as Oracle will automatically delete corresponding rows in the child table that have references to the parent table record being deleted. As no constraint specification is allowed in the ALTER TABLE…MODIFY clause, it cannot be used to add an ON DELETE CASCADE option to an already existing foreign key constraint. The constraint has to be dropped and created again with the ALTER TABLE…ADD syntax or the entire table can be created anew with a CREATE TABLE statement.

Steps

1. Run SQL*Plus and connect as the WAITE user account. CHP6_20.SQL, shown in Figure 6.20, drops the EMP06 table and creates the table again, this time with the ON DELETE CASCADE option, and also creates sample data used in this How-To.

SQL> START CHP6_20

SQL> SET ECHO OFF

Table dropped.

Table created.

1 row created.

1 row created.

1 row created.

When a department is deleted from the DEPT06 parent table, all associated records in the EMP06 child table should also be deleted. Sample records are created in both tables to demonstrate the process.

2. Run the statements in CHP6_21.SQL to query the DEPT06 and EMP06 tables, as shown in Figure 6.21.

There are two records in the EMP06 pointing to the DEPT06 record with DEPT_NO=10. Now execute the CHP6_22.SQL file, as shown in Figure 6.22, to delete the record in the DEPT06 parent table with DEPT_NO=10.

As the ON DELETE CASCADE option was specified in Step 1 while defining the foreign key constraint on EMP06 table, the two records in the EMP06 table pointing to the DEPT06 record with DEPT_NO=10 are deleted by Oracle automatically. Confirm this by running CHP6_21.SQL to query both tables again, as shown in Figure 6.23.

The record with a DEPT_NO=10 was deleted from the DEPT06 table. The corresponding two records in the EMP06 table with a DEPT_NO=10 were also automatically deleted.

How It Works

Cascading deletes can be defined with the ON DELETE CASCADE while creating a foreign key constraint. Properly defined cascade deletes ensure referential integrity is maintained in the database whenever DELETE statements are issued against referenced tables. Step 1 creates the EMP06 table with an ON DELETE CASCADE defined with its foreign key constraint. Step 2 demonstrates the automatic deletion of associated child table records from the EMP06 table, by deleting a record from the DEPT06 parent table.

Comments

Cascading deletes are ideal for enforcing referential integrity, and defining them with foreign key constraints is fairly easy. The cascading deletes come to action only when a DELETE statement is issued on a parent table record. Cascading deletes can be also performed by using DELETE triggers. See How-To 13.5 for a thorough discussion on how DELETE triggers can be used for cascading deletes. Depending on the size of tables, complexity of relationships, and performance requirements, cascading deletes can be implemented using different approaches: in the application itself, or ON DELETE CASCADE with foreign key constraints, or DELETE triggers.