SQL CASCADE

What does SQL CASCADE do?

CASCADE tells the database to automatically apply a DELETE, UPDATE, or DROP operation to dependent objects or rows.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CASCADE: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite (foreign keys only), Snowflake, Redshift

SQL CASCADE Full Explanation

CASCADE is a referential-action keyword defined in the SQL standard. When attached to a foreign-key clause (ON DELETE CASCADE or ON UPDATE CASCADE) it instructs the database to propagate the triggering row change to all child rows that reference it. This keeps referential integrity intact without manual cleanup.Many dialects also allow CASCADE with DDL statements such as DROP TABLE, DROP SCHEMA, or ALTER TABLE ... DROP CONSTRAINT. In those contexts CASCADE forces the database to drop or alter objects that depend on the target object (views, constraints, foreign keys, etc.) instead of raising an error.Key behaviors- ON DELETE CASCADE: deleting a parent row automatically deletes matching child rows.- ON UPDATE CASCADE: updating a parent key automatically updates the matching foreign keys in child rows.- DDL CASCADE: dropping an object removes all dependent objects.Caveats- Cascading deletes are irreversible inside the same statement, so use transactions if you might need an undo.- Overuse can hide accidental mass deletions or schema loss. Always combine with proper permissions, backups, and WHERE clauses.- Some engines (SQLite) support CASCADE only for foreign keys, not DDL.- Circular cascading paths are rejected or require special settings.

SQL CASCADE Syntax

-- Foreign key referential actions
FOREIGN KEY (child_col) REFERENCES parent(col)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

-- Drop objects with dependents
DROP TABLE table_name CASCADE;
ALTER TABLE table_name DROP CONSTRAINT fk_name CASCADE;
DROP SCHEMA reporting CASCADE;

SQL CASCADE Parameters

Example Queries Using SQL CASCADE

-- 1. Cascading delete and update
CREATE TABLE parent (
    id INT PRIMARY KEY,
    name TEXT
);

CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT REFERENCES parent(id)
                  ON DELETE CASCADE
                  ON UPDATE CASCADE,
    note TEXT
);

-- Delete a parent row; matching child rows vanish automatically
DELETE FROM parent WHERE id = 5;

-- Update the primary key; child rows follow
UPDATE parent SET id = 10 WHERE id = 8;

-- 2. Cascading drop
DROP TABLE parent CASCADE; -- removes child table and any dependent views

Expected Output Using SQL CASCADE

After DELETE, all rows in child with parent_id = 5 are removed.
After UPDATE, all rows in child previously pointing to 8 now point to 10.
DROP TABLE ... CASCADE deletes parent plus all dependent objects without error.

Use Cases with SQL CASCADE

Enforcing referential integrity without writing manual cleanup triggers
Automatically maintaining child keys when a primary key changes
Quickly removing a table, schema, or constraint along with all objects that rely on it during development or refactoring

Common Mistakes with SQL CASCADE

Forgetting that large delete cascades can wipe out many rows silently
Assuming CASCADE works without enabling foreign keys (SQLite requires PRAGMA foreign_keys = ON)
Expecting CASCADE to work across databases that do not support it for DDL operations (e.g., MySQL pre-8.0 lacks DROP TABLE ... CASCADE)
Using CASCADE on a self-referencing table without verifying that cycles are allowed

First Introduced In

SQL-92

Frequently Asked Questions

What is the difference between CASCADE and SET NULL?

CASCADE propagates the delete or update to child rows, removing or changing them. SET NULL leaves the child row intact but sets its foreign key column to NULL.

Can I disable CASCADE temporarily?

Most databases require altering the constraint to a different action or dropping it entirely. Alternatively, wrap the operation in a transaction and roll back if the result is not desired.

How do I find tables affected by a CASCADE drop?

Use system catalogs or INFORMATION_SCHEMA views (e.g., pg_depend in PostgreSQL) to list dependent objects before running DROP ... CASCADE.

Does CASCADE affect performance?

Cascading operations add extra work for the database engine. For large tables, deletes or updates may be slower. Index foreign key columns and batch large operations to mitigate impact.