PHPRO.ORG

  • tutorials
  • MySQL Referencial Integrity With ON DELETE CASCADE

MySQL Referencial Integrity With ON DELETE CASCADE

09 Sep

MySQL Referencial Integrity With ON DELETE CASCADE

By Kevin Waterson 6285 12 Comments

Contents

  1. Abstract
  2. Setting Up
  3. The Problem
  4. The Solution
  5. ON DELETE CASCADE

Abstract

Setting Up

For the purposes of this tutorial, the following tables and data will be used.


CREATE TABLE category(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
description TEXT
) engine=innodb;
INSERT INTO category( name, description ) VALUES ( 'Sports', 'Fun for the family' );
INSERT INTO category( name, description ) VALUES ( 'Family', 'Soccer Mums Wagon' );
INSERT INTO category( name, description ) VALUES ( 'Utility', 'For the workers' );
INSERT INTO category( name, description ) VALUES ( '4x4', 'Off Road Adventure' );
INSERT INTO category( name, description ) VALUES ( 'Motor Cycle', 'Born to be Mild' );

CREATE TABLE vehicle(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
make VARCHAR(100) NOT NULL,
model VARCHAR(50) NOT NULL,
category_id INT NOT NULL
) engine=innodb;
INSERT INTO vehicle ( id, make, model, category_id ) VALUES ( 1, 'Ford', 'Cortina', 2 );
INSERT INTO vehicle ( id, make, model, category_id ) VALUES ( 2, 'Ford', 'Falcon', 2 );
INSERT INTO vehicle ( id, make, model, category_id ) VALUES ( 3, 'Toyota', 'Hilux', 3 );
INSERT INTO vehicle ( id, make, model, category_id ) VALUES ( 4, 'Chevrolet', 'Corvette', 1 );
INSERT INTO vehicle ( id, make, model, category_id ) VALUES ( 5, 'Jeep', 'Cherokee', 4 );
INSERT INTO vehicle ( id, make, model, category_id ) VALUES ( 6, 'Harley Davidson', 'sportster', 5 );

CREATE TABLE vehicle_meta(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
value VARCHAR(150) NOT NULL,
vehicle_id INT NOT NULL
) engine=innodb;
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 1, 'Doors', '2', 1 );
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 2, 'Shape', 'Coupe', 1 );
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 3, 'Color', 'black', 1 );

INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 4, 'Doors', '4', 2 );
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 5, 'Shape', 'Wagon', 2 );
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 6, 'Color', 'blue', 2 );

INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 7, 'Doors', '2', 3 );
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 8, 'Shape', 'Pick Up', 3 );
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 9, 'Color', 'Yellow', 3 );

INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 10, 'Doors', '2', 4 );
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 11, 'Shape', 'Coupe', 4 );
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 12, 'Color', 'Red', 4 );

INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 13, 'Doors', '4', 5 );
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 14, 'Shape', 'Wagon', 5 );
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 15, 'Color', 'Silver', 5 );

INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 16, 'Shape', 'Sportster', 6 );
INSERT INTO vehicle_meta( id, name, value, vehicle_id ) VALUES ( 17, 'Color', 'Black', 4);

Having a quick look at the category and vehicle meta tables, the vehicles can be easily seen

The Problem

SELECT c.id, c.name, c.description, v.make, v.model FROM category c JOIN vehicle v ON c.id=v.category_id; 
+----+-------------+--------------------+-----------------+-----------+
| id | name        | description        | make            | model     |
+----+-------------+--------------------+-----------------+-----------+
|  1 | Sports      | Fun for the family | Chevrolet       | Corvette  |
|  2 | Family      | Soccer Mums Wagon  | Ford            | Cortina   |
|  2 | Family      | Soccer Mums Wagon  | Ford            | Falcon    |
|  3 | Utility     | For the workers    | Toyota          | Hilux     |
|  4 | 4x4         | Off Road Adventure | Jeep            | Cherokee  |
|  5 | Motor Cycle | Born to be Mild    | Harley Davidson | sportster |
+----+-------------+--------------------+-----------------+-----------+

Now, lets look at the meta data about one of the vehicles:

SELECT v.id, v.make, v.model, mv.name, mv.value FROM vehicle v JOIN vehicle_meta mv ON v.id=mv.vehicle_id WHERE v.id=2`; 
+----+------+--------+-------+-------+
| id | make | model  | name  | value |
+----+------+--------+-------+-------+
|  2 | Ford | Falcon | Doors | 4     |
|  2 | Ford | Falcon | Shape | Wagon |
|  2 | Ford | Falcon | Color | blue  |
+----+------+--------+-------+-------+

These tables above work fine as they are, however, there are a few issues. There is nothing to stop a record being added to the vehicle table with a category_id that does not exist. Similarly, the vehicle_meta table has no constraint on requiring a vehicle exist before adding meta data. The referential integrity fails.

The next problem arises when vehicle is deleted from vehicle table. There is no constraint which enforces that meta data for a vehicle must have a valid vehicle_id. If a vehicle is deleted, then the vehicle_meta data refering to that vehicle is orphaned.

These same principles apply to the relationship with the category and the vehicle tables. There is nothing to enforce referential integrity, and in short time, the data is a mess.

There is yet another issue should any of the parent (referenced) tables be updated and the id changes. Once again, the child (referencing) table data is orphaned, or worse, a vehicle may be assigned that vehicle_id and now the vehicle_meta data is incorrect. Getting really messy now.

The Solution

To fix the issue of referential integrity, a FOREIGN KEY is used. A FOREIGN KEY binds tables together by ensuring that a row record in a child table MUST have a valid parent. These are know as the "referenced" and "referencing" tables.

Adding a foreign key to a table is quite simple, here we add a FOREIGN KEY to the vehicle table to enforce the relationship with the category table.

ALTER TABLE vehicle
ADD CONSTRAINT fk_vehicle_category
FOREIGN KEY (category_id) REFERENCES category(id);

The query above sets a constraint, or binding between the vehicle and category tables. Deleting of a category which has data records referencing is no not possible.

DELETE FROM category WHERE id=5;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`vehicle`, CONSTRAINT `fk_vehicle_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`))

So powerful is the constraint that even the table itself cannot be dropped

DROP table category;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

This same principle can be applied to the vehicle_meta table with the same results. However, this alone does not fix all the issues. If the data is constrained to data in other tables, how then can the data be updated or deleted.

One method would be to delete the data in the vehicle_meta table with the id of the vehicles in a category you wanted to delete, then delete all the vehicles in the vehicle table from the given category, then delete the category.

What is needed is a method to CASCADE down through the relationships and update or delete the data as required. This is where ON UPDATE CASCADE and ON DELETE CASCADE do all the work for you. No more searching for orphaned data, or trying to relink data whos parent has been updated.

If you have created the FOREIGN KEY above, remove it with this line;

ALTER TABLE vehicle DROP FOREIGN KEY fk_vehicle_category;

ON DELETE CASCADE

By adding the FOREIGN KEY with the additional CONSTRAINTS, the referential integrity of the data is assured.

Two keys need to be added to accomplish this referential integrity.

  1. Link the vehicle table to the category table
  2. Link the vehicle_meta table to the vehicle table
    3. ALTER TABLE vehicle
    ADD CONSTRAINT fk_vehicle_category
    FOREIGN KEY (category_id) REFERENCES category(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

    ALTER TABLE vehicle_meta
    ADD CONSTRAINT fk_vehicle_vehicle_meta
    FOREIGN KEY (vehicle_id) REFERENCES vehicle(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

    Now if we delete a record from the vehicle table, the data in the vehicle_meta table will be removed also, as the would be orphaned if it remained.

    Looking at the vehicle_meta table, the vehicle_meta data for the Harley Davidson motorcycle is as follows:

    SELECT id, name, value FROM vehicle_meta WHERE vehicle_id=6; 
    +----+-------+-----------+
| id | name  | value     |
+----+-------+-----------+
| 16 | Shape | Sportster |
+----+-------+-----------+

    Now lets delete the Harley, and see what happens to the vehicle_meta data.

    DELETE FROM vehicle WHERE id=6;
    SELECT id, name, value FROM vehicle_meta WHERE vehicle_id=6;

    As you can see, the database has done its magic. The delete has cascaded to remove data which it is related to.

    There are two constraints existing on these tables, the other refers to the relationship between the vehicle and category tables. Can you guess what will happen if a category is deleted?

    A quick look at all the data fro vehicles made by Ford shows this

    SELECT c.name, c.description, v.make, v.model, mv.name, mv.value FROM category c JOIN vehicle v ON c.id=v.category_id JOIN vehicle_meta mv ON v.id=mv.vehicle_id WHERE v.make='Ford'; 
    +--------+-------------------+------+---------+-------+-------+
| name   | description       | make | model   | name  | value |
+--------+-------------------+------+---------+-------+-------+
| Family | Soccer Mums Wagon | Ford | Cortina | Doors | 2     |
| Family | Soccer Mums Wagon | Ford | Cortina | Shape | Coupe |
| Family | Soccer Mums Wagon | Ford | Cortina | Color | black |
| Family | Soccer Mums Wagon | Ford | Falcon  | Doors | 4     |
| Family | Soccer Mums Wagon | Ford | Falcon  | Shape | Wagon |
| Family | Soccer Mums Wagon | Ford | Falcon  | Color | blue  |
+--------+-------------------+------+---------+-------+-------+

    There are two cars in the table manufactured by Ford. both are in the Family cateogory. Lets see what happens to the data when the Family category is deleted.

    DELETE FROM category WHERE name='Family';

    Now lets see the vehicle table

    SELECT * FROM vehicle; 
    +----+-----------+----------+-------------+
| id | make      | model    | category_id |
+----+-----------+----------+-------------+
|  3 | Toyota    | Hilux    |           3 |
|  4 | Chevrolet | Corvette |           1 |
|  5 | Jeep      | Cherokee |           4 |
+----+-----------+----------+-------------+

    Quite plainly the Ford vehicles, both of which belonged to the Family category are now gone. A further look at the vehicle_meta table shows that all records relating to the vehicles has been delete also.

    The use of ON DELETE CASCADE and ON DELETE UPDATE give you great power to wipe out lots of data with very litte effort. Remember, with great power come great responsibility. Be careful.

    Oh, and if you see a homeless guy on the street, buy him a coffee. Peace out!