How to use ON DELETE CASCADE in MySQL

ON DELETE CASCADE is a helpful feature of MySQL that allows us to automatically delete related items in child tables when a record is deleted from a parent table. This feature can make database administration easier and simplify your database management tasks. In this post, we’ll look at how MySQL’s ON DELETE CASCADE feature works and how you can manage your database better.

Related Articles:

This article will cover:

What is ON DELETE CASCADE in MySQL
defining a foreign key constraint on the child table
Setting the "ON DELETE CASCADE" option in the foreign key definition
DELETE CASCADE MySQL query example
Conclusion
You might also like:

What is ON DELETE CASCADE in MySQL

deleted from the parent table. However, the default behaviour of the database server prevents you from deleting data in a table if other tables reference it. So if you want to delete rows in a child table when corresponding rows are deleted in the parent table, you need to specify the on delete cascade.
When a record is deleted from the parent table, the cascading delete feature in MySQL also deletes any associated items in the child tables. For instance, if a row in a “parent” table is associated with multiple rows in a “child” table, removing the row in the parent table will also result in deleting the related rows in the child table. The cascading-deletes feature will definitely reduce the quantity of SQL statements you need to perform delete actions.

Setting the “ON DELETE CASCADE” option in the foreign key definition and defining a foreign key constraint on the child table that references the parent table are the two steps required to enable this functionality.

Let’s say we have two tables in our database, “customers” and “orders”.

“customers” table: The “customers” table contains information about each customer and has a primary key “customer_id”.

 CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255),
  address VARCHAR(255)
);

“orders” table: the “orders” table contains information about the orders placed by each customer and has a primary key “order_id”.

 CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

So in order to enable delete cascade, we need to define the foreign key constraint on the “orders” table, which is the child table in this example then set up the ON DELETE CASCADE option in this foreign key.

defining a foreign key constraint on the child table

To specify the relationship with the parent table, we define a foreign key constraint in MySQL on the child table. The foreign key is a column or set of columns in the child table that refers to the parent table’s primary key. We specify the foreign key constraint to ensure that the data entered into the child table is consistent with the data in the parent table.

Let’s add to the “orders” table a foreign key “customer_id” that refers to the primary key of the “customers” table.

 CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Setting the “ON DELETE CASCADE” option in the foreign key definition

The “ON DELETE CASCADE” option in the foreign key definition must be set to enable cascade delete in MySQL. Following the stated cascade delete rules, MySQL will automatically delete any associated records in the child table when a record from the parent table is deleted. This can help you save time and effort and maintain consistency in your database.

Setting up the ON DELETE CASCADE option in this foreign key of the table orders.

 CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

Now, if you delete a customer from the “customers” table, all related orders in the “orders” table will be deleted automatically. It’s crucial to remember that cascade delete only functions when a record from the parent table is deleted. Deletions from the child table do not result in cascade deletes in the parent table.

DELETE CASCADE MySQL query example

The following query will delete the customers with “customer_id” 12 and 13 from the “customers” table and also delete all related orders in the “orders” table. The delete cascade feature reduces the quantity of SQL statements you need to perform delete actions, allowing you to manage your database more effectively.

  DELETE FROM customers WHERE customer_id = 12 AND customer_ID = 13;

Conclusion

Delete cascade is a powerful feature in MySQL, but you should be careful while using it. As you can see, deleting a record from the parent table means all related records in the child table will also be deleted. So Before you enable DELETE CASCADE, make sure you have a database backup.