Supabase Cascade: Mastering Relational Data Deletion

What is Supabase?

In a world increasingly driven by data, developers seek platforms that are powerful, scalable, and developer-friendly. Supabase is an open-source backend-as-a-service (BaaS) built on top of PostgreSQL, aiming to provide a Firebase alternative. It gives you instant APIs, authentication, real-time subscriptions, and more — all by just connecting to your PostgreSQL database.

Think of it as "PostgreSQL with batteries included." Supabase empowers teams to build full-stack applications faster, with less friction and better performance, thanks to the robust SQL database and modern tooling.

How Do We Delete Table Data in Supabase?

Deleting data in Supabase (or PostgreSQL, more broadly) is as simple as executing a SQL command or using Supabase's client libraries (like supabase-js).

Example via SQL:

DELETE FROM bookings WHERE id = 101

DELETE FROM bookings WHERE id = 101

DELETE FROM bookings WHERE id = 101

Example via Supabase JS:

This works fine when the table is standalone or has no dependencies. However, when the data is related to other tables — say, bookings linked to users or properties — deleting a row can have unintended consequences. That’s where ON DELETE rules come in.

How to Delete Data With Relationships?

Let’s say you have a basic real estate booking system with the following tables:

Table Schema Definition

ERD (Entity Relationship Diagram)

Example Modal Table With Mock Data

Users

Properties

Bookings

To delete data safely and consistently, especially in a relational schema like this, it's essential to define ON DELETE rules on foreign key constraints.

This leads us to the concept of Cascade behaviour in Supabase/PostgreSQL.

Exploring Cascade

Let’s dive into the types of ON DELETE rules and their real-world implications.

CASCADE

Definition: When a row in the parent table is deleted, all matching rows in the child table are automatically deleted.

Example Scenario:

A host deletes their account. All their properties and the bookings related to those properties should also be deleted to maintain consistency.

Schema:

ALTER TABLE properties
ADD CONSTRAINT fk_host
FOREIGN KEY (host_id)
REFERENCES users(id)
ON DELETE CASCADE

ALTER TABLE properties
ADD CONSTRAINT fk_host
FOREIGN KEY (host_id)
REFERENCES users(id)
ON DELETE CASCADE

ALTER TABLE properties
ADD CONSTRAINT fk_host
FOREIGN KEY (host_id)
REFERENCES users(id)
ON DELETE CASCADE

Outcome:

• Delete user → their properties get deleted.

• Properties deleted → bookings referencing them also get deleted.

Use With Caution: Can remove a large amount of data unintentionally.

RESTRICT

Definition: Prevents deletion of a row if it is referenced by any row in another table.

Example Scenario:

A guest has active bookings. Their account should not be deletable to avoid broken records.

Schema:

ALTER TABLE bookings
ADD CONSTRAINT fk_guest
FOREIGN KEY (guest_id)
REFERENCES users(id)
ON DELETE RESTRICT

ALTER TABLE bookings
ADD CONSTRAINT fk_guest
FOREIGN KEY (guest_id)
REFERENCES users(id)
ON DELETE RESTRICT

ALTER TABLE bookings
ADD CONSTRAINT fk_guest
FOREIGN KEY (guest_id)
REFERENCES users(id)
ON DELETE RESTRICT

Outcome:

Trying to delete a guest with bookings results in an error.

Best For: Critical referential enforcement.

SET NULL

Definition: When a parent row is deleted, the referencing foreign key in the child table is set to NULL.

Example Scenario:

A host deletes their account, but you want to keep their property listings. The host reference becomes NULL.

Schema:

ALTER TABLE properties
ADD CONSTRAINT fk_host
FOREIGN KEY (host_id)
REFERENCES users(id)
ON DELETE SET NULL

ALTER TABLE properties
ADD CONSTRAINT fk_host
FOREIGN KEY (host_id)
REFERENCES users(id)
ON DELETE SET NULL

ALTER TABLE properties
ADD CONSTRAINT fk_host
FOREIGN KEY (host_id)
REFERENCES users(id)
ON DELETE SET NULL

Outcome:

• User deleted → host_id becomes NULL in properties

Note: Ensure host_id column allows NULL values.

SET DEFAULT

Definition: When the parent is deleted, the child’s foreign key is set to a default value.

Example Scenario:

Have a placeholder user (e.g. ID 999) representing "Deleted User". Use this as fallback reference.

Schema:

ALTER TABLE properties
ALTER COLUMN host_id SET DEFAULT 999;

ALTER TABLE properties
ADD CONSTRAINT fk_host
FOREIGN KEY (host_id)
REFERENCES users(id)
ON DELETE SET DEFAULT

ALTER TABLE properties
ALTER COLUMN host_id SET DEFAULT 999;

ALTER TABLE properties
ADD CONSTRAINT fk_host
FOREIGN KEY (host_id)
REFERENCES users(id)
ON DELETE SET DEFAULT

ALTER TABLE properties
ALTER COLUMN host_id SET DEFAULT 999;

ALTER TABLE properties
ADD CONSTRAINT fk_host
FOREIGN KEY (host_id)
REFERENCES users(id)
ON DELETE SET DEFAULT

Outcome:

• User deleted → host_id becomes 999

Important: The default value must exist in the referenced table.

NO ACTION

Definition: Prevents deletion if referenced rows exist, but checks after the current transaction.

Example Scenario:

You're deleting multiple things — a user and their properties — within the same transaction. NO ACTION allows all deletes to happen before throwing errors.

Schema:

ALTER TABLE bookings
ADD CONSTRAINT fk_property
FOREIGN KEY (property_id)
REFERENCES properties(id)
ON DELETE NO ACTION

ALTER TABLE bookings
ADD CONSTRAINT fk_property
FOREIGN KEY (property_id)
REFERENCES properties(id)
ON DELETE NO ACTION

ALTER TABLE bookings
ADD CONSTRAINT fk_property
FOREIGN KEY (property_id)
REFERENCES properties(id)
ON DELETE NO ACTION

Outcome:

• Errors only if references still exist after transaction is complete.

Useful For: Complex transaction workflows.

NO ACTION INITIALLY DEFERRED

Definition: A deferred form of NO ACTION where the foreign key constraint is checked at the end of the transaction, not immediately.

Example Scenario:

You're removing a user, their properties, and bookings in one transaction. Deferred checking allows full deletion sequencing.

Schema:

ALTER TABLE bookings
ADD CONSTRAINT fk_property
FOREIGN KEY (property_id)
REFERENCES properties(id)
ON DELETE NO ACTION
DEFERRABLE INITIALLY DEFERRED

ALTER TABLE bookings
ADD CONSTRAINT fk_property
FOREIGN KEY (property_id)
REFERENCES properties(id)
ON DELETE NO ACTION
DEFERRABLE INITIALLY DEFERRED

ALTER TABLE bookings
ADD CONSTRAINT fk_property
FOREIGN KEY (property_id)
REFERENCES properties(id)
ON DELETE NO ACTION
DEFERRABLE INITIALLY DEFERRED

Outcome:

• Constraint is only evaluated at COMMIT time.

Use When: You need advanced multi-step deletions.

Summary Table

Common Errors and Debugging CASCADE Issues in Supabase

Using ON DELETE CASCADE in Supabase (PostgreSQL) is powerful — but when something breaks, it’s rarely obvious why.

Here are the most common issues teams run into, and how to debug them.

1. Cascade not working as expected

What happens:
You delete a parent record, but related rows don’t get deleted.

Common causes:

• Foreign key constraint not set with ON DELETE CASCADE

• Constraint added incorrectly or after data was inserted

• Wrong column referenced in the relationship

How to fix:

• Check your foreign key definition in the Supabase dashboard or SQL editor

• Ensure it explicitly includes ON DELETE CASCADE

• Recreate the constraint if needed

2. Existing data not cascading

What happens:
New records cascade correctly, but older data doesn’t behave the same way.

Why this happens:

• The constraint was added after the data already existed

• Existing rows may violate the constraint rules

Fix:

• Clean or backfill inconsistent data

• Reapply constraints properly

3. Accidental mass deletion

What happens:
Deleting one record removes way more data than expected.

Why this happens:

• Multiple tables are chained with cascade relationships

• One delete triggers a full cascade chain

How to avoid it:

• Always map relationships before enabling cascade

• Test deletes in a staging environment

• Use soft deletes (is_deleted) if needed

4. Row-Level Security (RLS) conflicts

What happens:
Delete operation fails or behaves inconsistently.

Why this happens:

• RLS policies block cascading deletes

• Permissions are not properly configured

Fix:

• Ensure delete policies allow the operation

• Check if cascading actions respect user roles

5. Foreign key constraint errors

Error example:

“update or delete on table violates foreign key constraint”

Why this happens:

• ON DELETE CASCADE is not enabled

• Default behavior (RESTRICT) is blocking deletion

Fix:

• Update constraint to include cascade

• Or manually delete dependent rows before deleting parent

6. Debugging tips that save time

Instead of guessing, follow a simple checklist:

• Inspect foreign key constraints in SQL

• Check relationship chains between tables

• Verify RLS policies

• Test deletes on sample data

• Use logs or Supabase dashboard queries 

Final Thoughts

Understanding ON DELETE actions is essential when building a robust Supabase (or PostgreSQL) application. Whether you're building a booking platform, a blog, or a social app data integrity is key. Use cascading rules thoughtfully based on your application's needs.

Further Reading:

• Supabase Cascade Deletes

• PostgreSQL ON DELETE Docs

• Supabase Foreign Key Setup

• Using Supabase JS

Stay tuned for more in-depth Supabase guides!