SQL ON DELETE CASCADE: Automatically Remove Dependent Data

Deleting data in SQL isn’t just about removing a single row. In a relational database, tables are often linked together, and a delete in one table can impact other tables. To manage this safely, SQL uses foreign key constraints to define how related data should behave when rows are deleted or updated.

One of the most powerful delete options is the SQL ON DELETE CASCADE constraint. Instead of blocking a delete or leaving orphaned records behind, this rule tells the database to automatically remove all dependent child rows when a referenced parent row is deleted.

In this tutorial, I will show how the ON DELETE CASCADE constraint works, why databases support automatic cascading deletes, and when it’s the right choice compared to more restrictive rules like ON DELETE RESTRICT.  If you are new to SQL, start with our Introduction to SQL course, or the Intermediate SQL course if you have some experience. 

What Is ON DELETE CASCADE in SQL?

To understand how the ON DELETE CASCADE foreign key constraint works, let me first explain referential integrity and the CASCADE clause.

Referential integrity and foreign keys

In relational databases, tables are connected through foreign keys, which link a column in one table (the child) to a primary key in another table (the parent). Foreign keys enforce referential integrity to ensure that a child row always points to a valid parent row. 

Without this enforcement, databases could easily end up with orphaned records, where rows reference data that no longer exists. Therefore, delete rules exist to tell the database exactly how to handle these orphans before they even happen.

What CASCADE actually means

In SQL, the ON DELETE CASCADE constraint instructs the database to automatically delete all child rows referencing the deleted parent row. It triggers the operation recursively through foreign key chains, deleting dependent data in one atomic transaction.

For example, you can delete a customer record in the database, and it cascades to erase their orders, which in turn deletes order items in a single query.

How ON DELETE CASCADE Works in Practice

Now that you have understood what CASCADE does, let’s look at how the database applies the logic.

Parent and child table relationships

As you now know, database relationships have one table that acts as the parent, holding the primary key, while another acts as the child, storing that key as a foreign key. When you query a DELETE statement on the parent table, the database engine first checks for foreign key constraints to identify all dependents through indexing for speed.

What happens when a parent row is deleted

When you execute a DELETE command on a parent row, the database pauses the final execution to check its foreign key rules in the following order:

• Identification: The engine looks at the foreign key index to find all rows in child tables that match the ID being deleted.
• Execution: Before (or during) the removal of the parent row, the engine deletes the identified child rows.
• Validation: The database ensures that no orphaned rows remain. If all deletes are successful, the transaction is finalized.

You should note that the above process will only happen if you have defined the constraint ON DELETE CASCADE, as we will see later in the article.

ON DELETE CASCADE vs Other Delete Rules

In SQL, there are different DELETE rules that tell the database how to handle data related to different tables. In this section, we will compare ON DELETE CASCADE with these constraints.

CASCADE vs. RESTRICT

From the above explanation, we see that CASCADE automatically deletes all dependent child rows when a parent row is removed, ensuring no orphaned records remain.

However, SQL RESTRICT constraint blocks the delete operation entirely if any child rows still reference the parent row. This operation ensures no accidental data loss. In the example below, the RESTRICT constraint prevents deleting a customer if there are any orders that reference that customer.

-- Parent table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Child table with ON DELETE RESTRICT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);

CASCADE vs. SET NULL and SET DEFAULT

While CASCADE removes dependent rows entirely, SET NULL and SET DEFAULT preserve the child rows in the following ways:

When you define your constraint as ON DELETE SET NULL, the parent row is deleted, but the foreign key values in the child table are set to NULL.

In the example below, deleting a customer does not remove the associated orders. Instead, the customer_id column in the orders table is automatically set to NULL. This preserves the order records while breaking the relationship with the deleted customer.

-- Child table using ON DELETE SET NULL
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET NULL
);

Similarly, with the SET DEFAULT option, deleting a customer does not remove related orders. Instead, the customer_id in the orders table is reset to its defined default value. This keeps the order records intact while assigning them a predefined placeholder.

-- Child table using ON DELETE SET DEFAULT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT DEFAULT 0,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET DEFAULT
);

Creating Foreign Keys with ON DELETE CASCADE

To use the ON DELETE RESTRICT constraint in SQL, you must specify it when establishing the foreign key relationship between the parent and child tables.

CASCADE in table schemas

You can define the ON DELETE CASCADE rule in SQL either when creating tables or by modifying existing tables.

The example below shows how to apply the SQL ON DELETE CASCADE foreign key constraint when defining a new table. With CASCADE, deleting a department automatically removes all employees associated with that department.

-- Stores department details
CREATE TABLE departments (
    department_id INT PRIMARY KEY, 
    department_name VARCHAR(100)
);

-- Automatically deletes employees when their department is deleted
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,      -- Unique employee identifier
    department_id INT, 
    CONSTRAINT fk_employees_departments
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE CASCADE
);

If the table already exists, you can add a foreign key constraint with ON DELETE CASCADE. Therefore, if you delete a department, it automatically deletes all related employee records.

-- Adds a foreign key constraint after the table already exists
ALTER TABLE employees
ADD CONSTRAINT fk_employees_departments
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE CASCADE;    -- Deletes employees when the related department is deleted

Cascading chains and multi-level deletes

One of the powerful features of CASCADE is that the deletes don’t stop at a single relationship. If a child table is also a parent to another table, the delete can propagate through multiple levels, called cascading chains. For example, deleting a user might delete orders, which then delete order items, which may delete related audit rows.

Therefore, it is important to document and carefully review schemas that use CASCADE. Since a single delete statement can affect many tables, you should understand the full dependency chain before enabling cascading behavior.

Common Use Cases for ON DELETE CASCADE

The ON DELETE CASCADE constraint in SQL is important in scenarios where data in the child table has no value without the parent record. Let’s look at its application in real-world database management.

Automatically cleaning up dependent data

You can use ON DELETE CASCADE to clean up data where manual cleanup would be inefficient. These include tables such as logs, history records, audit trails, or many-to-many join tables that often exist solely to support a parent record. For example:

• Deleting a user should remove their login history
• Deleting an order should remove its order items
• Deleting a product should remove entries in a product-category mapping table

Simplifying data lifecycle management

You can also use CASCADE to move data lifecycle rules into the database. Instead of relying on application code to find related records, delete them in the correct order, and handle edge cases and failures, the database enforces these rules consistently and atomically.

In this way, CASCADE ensures that no matter how a delete is triggered, whether through an application, a script, or an admin query, the outcome is always consistent.

Risks and Pitfalls of ON DELETE CASCADE

Although the ON DELETE CASCADE constraint can be convenient, it can be risky when used incorrectly. Understanding the common pitfalls is important in designing efficient CASCADE constraints.

Accidental mass deletions

The most common risk with CASCADE is unintended large-scale deletes. A single DELETE statement on a parent table can silently remove hundreds or thousands of related rows across multiple tables. This action can result in unexpected data loss that may be difficult to recover if you didn’t properly understand the relationship between tables. 

As a best practice, always ensure you can trace the relationship between foreign keys in parent and child tables. If you get the schema right, you will know which tables would get affected by a single delete.

Hidden dependencies in complex schemas

As databases grow, tables accumulate additional foreign keys, and documentation can fall out of sync with reality. The challenge comes when CASCADE is implemented in such environments, and deletes can propagate through deeply nested dependency chains that developers or administrators aren’t aware of.

From my experience, I recommend you pair CASCADE with clear schema documentation and regular review of foreign key relationships.

Try out our SQL Server for Database Administrators skill track if you regularly design or maintain databases as part of your work. 

Database-Specific Notes and Behavior

While the concept of ON DELETE CASCADE is standard across relational databases, each engine handles the underlying execution with slight differences.

PostgreSQL, MySQL, and SQL Server behavior

PostgreSQL, MySQL, and SQL Server all support ON DELETE CASCADE and implement it same way, with the following exceptions:

• MySQL: CASCADE works well with InnoDB, but avoids circular cascades

• PostgreSQL: Most flexible and permissive with cascading delete chains

• SQL Server: Safer but more restrictive due to multiple cascade path rules

Transaction scope and rollback safety

Cascading deletes are executed within the same transaction as the original delete statement. If the database encounters an error while trying to delete a child row, the entire operation fails. The parent row is not deleted, and any already-removed child rows are restored.

This transactional behavior provides an important safety net. It allows you to test, review, or reverse cascading deletes before committing.

Choosing the Right ON DELETE Strategy

By now, you know that choosing the correct delete strategy depends on how your data is related and how you want it to behave over time. Consider the following guidelines to make the right DELETE choice for your schema.

When CASCADE is the right choice

You should use the ON DELETE CASCADE constraint when the child data is completely dependent on the parent for its meaning or existence. For example, you can use it in the following:

• Join tables in many-to-many relationships
• Temporary or derived data, such as logs or history entries

In the above cases, automatic cleanup ensures consistency while reducing the manual delete logic required in the application layer.

I recommend taking our Joining Data in SQL course to learn the different types of joins in SQL and how to work with different related tables in the database.

When to avoid CASCADE

However, you should avoid the ON DELETE CASCADE constraint in your schema if the child data has an independent value or must be preserved for legal, auditing, or analytical purposes. For example, you may not use CASCADE in:

• Financial records and invoices
• Audit logs and compliance data, such as healthcare data.

For such cases, I recommend using restrictive rules, such as RESTRICT, SET NULL, or application-managed deletes to provide greater control and visibility.

Conclusion

SQL ON DELETE CASCADE constraint is a powerful automation tool that preserves referential integrity by automatically removing dependent data when a parent row is deleted. When used appropriately, it simplifies cleanup, reduces manual delete logic, and ensures consistent behavior across applications and scripts. To avoid unintended deletions, always be cautious when using CASCADE if you don’t fully understand the relationships in the database schema.

I recommend taking our Database Design course, where you will learn to create and manage databases and select the appropriate DBMS for your needs. I also recommend trying out our Associate Data Engineer in SQL career track to learn the fundamentals of data engineering and data warehousing.