Foreign key ON DELETE behavior in SQL

Introduction

Foreign key ON DELETE behavior controls what happens to related data when a record is deleted. It helps keep data correct and avoids broken links.

When you want to delete a customer and also remove all their orders automatically.

When you want to prevent deleting a product if there are still orders using it.

When you want to set a related field to empty if the linked record is deleted.

When you want to keep related data unchanged even if the linked record is deleted.

Syntax

SQL

FOREIGN KEY (child_column) REFERENCES parent_table(parent_column) ON DELETE action

The action can be CASCADE, SET NULL, RESTRICT, NO ACTION, or SET DEFAULT.

This rule applies only when deleting rows from the parent table.

Examples

Deletes all orders if the related customer is deleted.

SQL

FOREIGN KEY (order_customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE

Sets the product ID to NULL in orders if the product is deleted.

SQL

FOREIGN KEY (order_product_id) REFERENCES products(product_id) ON DELETE SET NULL

Prevents deleting an employee if they are referenced in another table.

SQL

FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT

Sample Program

This example creates two tables with a foreign key that deletes orders when the related customer is deleted. After deleting customer 1, orders linked to customer 1 are also removed.

SQL

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

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

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO orders VALUES (101, 1), (102, 1), (103, 2);

DELETE FROM customers WHERE customer_id = 1;

SELECT * FROM orders ORDER BY order_id;

Important Notes

CASCADE deletes related rows automatically.

SET NULL requires the foreign key column to allow NULL values.

RESTRICT and NO ACTION prevent deletion if related rows exist.

Summary

ON DELETE controls what happens to related rows when a parent row is deleted.

Common actions are CASCADE (delete related), SET NULL (clear related), and RESTRICT (block delete).

Use ON DELETE to keep your data consistent and avoid orphaned records.