MySQL Cookbook

Deleting Related Rows in Multiple Tables

Problem

You want to delete related records from multiple tables. This is common, for example, when you have tables that are related in master-detail or parent-child fashion; deleting a parent record typically requires all the associated child records to be deleted as well.

Solution

You have several options. MySQL 4.0 supports cascaded delete with a multiple-table DELETE syntax; you can replace the table with new versions that contain only the records not to be deleted; you can write a program to construct appropriate DELETE statements for each table, or you may be able to use mysql to do so.

Discussion

Applications that use related tables need to operate on both tables at once for many operations. Suppose you use MySQL to record information about the contents of software distributions that you maintain. The master (or parent) table lists each distribution’s name, version number, and release date. The detail (or child) table lists information about the files in the distributions, thus serving as the manifest for each distribution’s contents. To allow the parent and child records to be associated, each parent record has a unique ID number, and that number is stored in the child records. The tables might be defined something like this:

CREATE TABLE swdist_head ( dist_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # distribution ID name VARCHAR(40), # distribution name ver_num NUMERIC(5,2), # version number rel_date DATE NOT NULL, # release date PRIMARY ...