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

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

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

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

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

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

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

Outcome:

• Constraint is only evaluated at COMMIT time.

Use When: You need advanced multi-step deletions.

Summary Table

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!