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)

• Share This:
  
• Bookmark on Delicious
• Digg this post
• Recommend on Facebook
• Share on Linkedin
• share via Reddit
• Share with Stumblers
• Tweet about it
• Subscribe to the comments on this post
• Tell a friend

Related posts:

1. PostgreSQL Stored Procedures – Part 2
2. Powerful Data Insertion Features in MySQL
3. PostgreSQL Introduction
4. Database Definition Languages
5. Issues With MySQL

0 Comment   |   Posted in Database,SQL,blog by lcleveland on September 11, 2008