Posted on

On Delete Cascade

ON DELETE CASCADE

In a relational database where would you use the following construct? What does it do?

 … ON DELETE CASCADE

Answer

ON DELETE CASCADE is used when creating a table that has a field with a FOREIGN KEY. The purpose it to ensure that when a row in the parent table is deleted all of the related rows in the CHILD table are deleted.

Example

 CREATE TABLE cities (
       city     varchar(80) primary key
 );

 CREATE TABLE weather_history (
       city      varchar(80) references cities(city) ON DELETE CASCADE,
       temp_lo   int,
       temp_hi   int,
       date      date
  );

The parent table is cities, the child is weather_history. We normally load it up so that we can get reports like this:

 New Orleans
    Jan 1 , 60f 70f
    Jan 2 , 61f 74f
 Charleston
    Jan 1 , 35f 50f
    Jan 2 , 45f 65f

If New Orleans gets washed into the ocean due to a hurricane, we can now get rid of it like this:

 DELETE FROM cities where city='New Orleans';

The ON DELETE CASCADE ensures all the related records in weather_history are removed, preventing orphans (a bunch of records in weather history that have no city attached to them)