Delete (SQL) - Example With Related Tables

Example With Related Tables

Suppose there is a simple database that lists people and addresses. More than one person can live at a particular address and a person can live at more than one address (this is an example of a many-to-many relationship). The database only has three tables, person, address, and pa, with the following data:

person

pid	name
1	Joe
2	Bob
3	Ann

address

aid	description
100	2001 Main St.
200	35 Pico Blvd.

pa

pid	aid
1	100
2	100
3	100
1	200

The pa table relates the person and address tables, showing that Joe, Bob and Ann all live at 2001 Main Street, but Joe also takes up residence on Pico Boulevard.

In order to remove joe from the database, two deletes must be executed:

DELETE FROM person WHERE pid=1; DELETE FROM pa WHERE pid=1;

To maintain referential integrity, Joe's records must be removed from both person and pa. The means by which integrity is sustained can happen differently in varying relational database management systems. It could be that beyond just having three tables, the database also has been set up with a trigger so that whenever a row is deleted from person any linked rows would be deleted from pa. Then the first statement:

DELETE FROM person WHERE pid=1;

would automatically trigger the second:

DELETE FROM pa WHERE pid=1;