Prohibit CASCADE: A SQL Review Rule Explained

Before getting into the rule, let's be clear about what we are guarding against. Bytebase SQL Review ships two rules that exist for one reason: to stop a CASCADE from quietly taking down more than you asked for.

1. Prohibit using CASCADE option for ON DELETE clauses: The CASCADE option in ON DELETE can cause a large number of dependent objects to be deleted or modified, leading to unexpected results.

2. Prohibit using CASCADE when removing a table: Using the CASCADE option when removing a table can cause a large number of dependent objects to be deleted or modified, leading to unexpected results.

What is CASCADE?#

CASCADE shows up in two contexts in SQL, and the second one is where people get burned.

CASCADE in Foreign Key Constraints#

CREATE TABLE posts (
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE  -- Deleting a user deletes all their posts
);

CASCADE in TRUNCATE Operations#

TRUNCATE TABLE users CASCADE;  -- Truncates users AND all tables referencing it

Note: TRUNCATE ... CASCADE is PostgreSQL-specific. This behavior (automatically truncating dependent tables via foreign keys) doesn't exist in MySQL, SQL Server, or other databases. Oracle has TRUNCATE ... CASCADE but for a different purpose (materialized views).

This one is dangerous precisely because so many developers think they know what it does.

The Common Misunderstanding#

Imagine a team migrating their database and wanting to clean up test data from some new tables. A developer runs:

TRUNCATE TABLE new_feature_table CASCADE;

Their intention: Delete test data from the new tables.

Their assumption: CASCADE only deletes related test data in other tables.

What actually happened: TRUNCATE ... CASCADE deleted ALL data in every table with foreign keys pointing to new_feature_table, including years of production data.

Here is the key. TRUNCATE TABLE A CASCADE does not just delete related rows in table B. It truncates the entire tables that have foreign keys pointing to table A. In a complex schema where tables are interconnected, a single CASCADE can empty out your whole database. Think of it like pulling one thread and watching the entire sweater unravel, except the sweater is your production data.

How to Protect Your Database#

1. Use explicit DELETE statements

-- ❌ DANGEROUS: CASCADE affects unexpected tables
TRUNCATE TABLE users CASCADE;

-- ✅ SAFE: Full control over what's deleted
DELETE FROM user_sessions WHERE user_id IN (SELECT id FROM users WHERE ...);
DELETE FROM users WHERE ...;

2. Implement automated SQL review

Use Bytebase to scan every SQL statement before it runs and block dangerous CASCADE operations in production.

3. Remove dangerous privileges

REVOKE TRUNCATE ON ALL TABLES IN SCHEMA public FROM app_user;

4. Test migrations thoroughly

Run the full migration in staging with production-like data, verify the scope of impact, and test the rollback before you touch prod.

5. Use safer foreign key options

Consider ON DELETE RESTRICT or ON DELETE SET NULL instead of ON DELETE CASCADE.

Summary#

CASCADE can trigger automatic data deletion across your entire database, and the one fact to remember is this: TRUNCATE TABLE A CASCADE truncates ALL tables with foreign keys pointing to A, not just the related rows.

Protect your database:

• Use explicit DELETE statements for full control over data deletion
• Block CASCADE in production using Bytebase SQL review rules
• Remove TRUNCATE privileges from application users
• Always test schema changes in staging environments

CASCADE is convenient until the day it isn't. When the blast radius is your production data, convenient is not worth it.