On Delete Cascade in SQL

In this article, you’ll learn what the ON DELETE CASCADE keyword means and what it does.

What is On Delete Cascade in SQL?

The ON DELETE CASCADE is an option when defining a foreign key in SQL. It indicates that related rows should be deleted in the child table when rows are deleted in the parent table.

If you don’t specify this option, then you’ll get an error when you attempt to delete rows in the parent table, saying that “child rows exist”.

This ON DELETE CASCADE feature is available in Oracle, SQL Server, MySQL, and Postgres (and likely many other SQL database vendors).

 

Which Direction Does the On Delete Cascade Work?

You might be wondering which direction the On Delete Cascade should go. Which table do you put it on?

If we use the concept of a parent table and a child table, where a parent has many children, the On Delete Cascade means that there will be no orphan records or no children without parents.

The On Delete Cascade will go on the foreign key of the child table so that when you delete a parent, all children are deleted.

It goes on the foreign key because that’s where the relationship between the two tables is defined.

 

Example of On Delete Cascade

In this example, we will:

1. Set up some sample data without the On Delete Cascade feature
2. Delete data and see what happens
3. Add the feature
4. Delete data again and see what happens

 

Sample Data

Here’s the sample data. You can get a copy of this script in my GitHub repository here.

We’ll use a category table and a product table. A product belongs to a category, so the parent table is category and the child table is product.

CREATE TABLE category (
    id INT,
    category_name VARCHAR(50),
    CONSTRAINT pk_category PRIMARY KEY (id)
);

CREATE TABLE product (
    id INT,
    category_id INT,
    product_name VARCHAR(50),
    CONSTRAINT pk_product PRIMARY KEY (id),
    CONSTRAINT fk_prod_category
      FOREIGN KEY (category_id) REFERENCES category(id)
);

INSERT INTO category (id, category_name) VALUES
(1, 'Electronics'),
(2, 'Furniture');

INSERT INTO product (id, category_id, product_name) VALUES
(1, 2, 'Desk Chair'),
(2, 2, 'Couch'),
(3, 1, 'TV'),
(4, 1, 'Coffee Machine');

We’ve added 2 category rows and 4 product rows.

 

Delete a Parent Row

Let’s delete the category of Electronics which has the ID of 1. There are 2 products in the Electronics category.

DELETE FROM category WHERE id = 1;

When you run this statement, you’ll get an error.

Here’s the error in MySQL:

Error Code: 1451. Cannot delete or update a parent row:
a foreign key constraint fails (`my_new_db`.`product`,
CONSTRAINT `fk_prod_category` FOREIGN KEY (`category_id`)
REFERENCES `category` (`id`))

You’ll get similar errors in other databases.

 

Add the On Delete Cascade

Let’s recreate the table by adding the On Delete Cascade option. We could alternatively do this with an Alter Table statement, but I want to show you how to do it from scratch.

DROP TABLE product;
DROP TABLE category;

CREATE TABLE category (
  id INT,
  category_name VARCHAR(50),
  CONSTRAINT pk_category PRIMARY KEY (id)
);

CREATE TABLE product (
  id INT,
  category_id INT,
  product_name VARCHAR(50),
  CONSTRAINT pk_product PRIMARY KEY (id),
  CONSTRAINT fk_prod_category
    FOREIGN KEY (category_id) REFERENCES category(id)
    ON DELETE CASCADE
);

We added the words ON DELETE CASCADE at the end of the Foreign Key definition. This means that whenever a row is deleted in the table referenced here (in this case, the category table), then all rows in the product table with the category_id that is deleted will also be deleted.

Let’s insert some data.

INSERT INTO category (id, category_name) VALUES
(1, 'Electronics'),
(2, 'Furniture');

INSERT INTO product (id, category_id, product_name) VALUES
(1, 2, 'Desk Chair'),
(2, 2, 'Couch'),
(3, 1, 'TV'),
(4, 1, 'Coffee Machine');

We’ve recreated the table and populated the data.

 

Delete Data Again

Now, we can try to delete the Electronics category.

DELETE FROM category WHERE id = 1;

This time the statement is successful:

1 row(s) affected.

Now we can select from the category table:

SELECT id, category_name
FROM category;

There is only one category:

id	category_name
2	Furniture

Here’s the data for the product table.

SELECT id, category_id, product_name
FROM product;

id	category_id	product_name
1	2	Desk Chair
2	2	Couch

We can see that the products in the category of Electronics have been deleted. We didn’t see they were deleted when we ran the Delete statement (it just said 1 row affected), but they were deleted as a side-effect due to the On Delete Cascade.

 

Conclusion

Adding the ON DELETE CASCADE to a foreign key in a child table will mean that any parent rows that get deleted will cause the related child rows to get deleted as well. It’s a handy feature, but you need to be sure it’s something you want when you implement it.