ON DELETE CASCADE
In a relational database where would you use the following construct? What does it do?
… ON DELETE CASCADE
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.
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)