Blog
3 min read

ON DELETE CASCADE Explained (and When Not to Use It)

When you delete a row that other rows reference, the foreign key decides what happens: block the delete, cascade it, or set the reference to NULL. How CASCADE, RESTRICT, SET NULL and NO ACTION work, examples, the risks of cascading, and how to change an existing constraint.

When one table references another — orders point at users, comments point at posts — you have to decide what happens if the referenced row is deleted. That's what ON DELETE on a foreign key controls. (Primary key vs foreign key)

The default: you can't delete it

CREATE TABLE comments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  post_id bigint REFERENCES posts(id),
  body text
);

Try to delete a post that has comments:

ERROR: update or delete on table "posts" violates foreign key constraint
"comments_post_id_fkey" on table "comments"
DETAIL: Key (id)=(7) is still referenced from table "comments".

The database protects you from leaving comments pointing at a post that no longer exists. That's the NO ACTION default.

The options

post_id bigint REFERENCES posts(id) ON DELETE CASCADE
Option When the referenced row is deleted…
NO ACTION (default) Error — delete is blocked (checked at end of statement)
RESTRICT Error — blocked immediately
CASCADE The referencing rows are deleted too
SET NULL The referencing column is set to NULL
SET DEFAULT The referencing column is set to its default

CASCADE: delete the children too

CREATE TABLE comments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  body text
);

DELETE FROM posts WHERE id = 7;   -- also deletes all of post 7's comments

Good when the child can't meaningfully exist without the parent:

  • comments on a post
  • items in an order
  • rows in a join table like post_tags (Database relationships)
  • a user's sessions or settings when the user is deleted
assigned_to bigint REFERENCES users(id) ON DELETE SET NULL

When the user is deleted, their tasks remain, unassigned. Good when the child has value on its own. The column must allow NULL. (NULL in SQL)

RESTRICT / NO ACTION: make deletion deliberate

Good when deleting the parent should never quietly affect other data:

  • invoices and payments (you need them for accounting)
  • orders referencing products (archive the product instead)
  • anything you'd be in trouble for losing

The risk with CASCADE

Cascades chain. Delete a user → cascades to their projects → cascades to tasks → cascades to comments and attachments. One DELETE can remove thousands of rows across many tables, with no warning.

This matters more when an AI agent or a script is running SQL for you. A cascading delete on a production database is one of the fastest ways to lose a lot of data. (How to stop an AI agent deleting your production database)

Safeguards:

Changing an existing constraint

You can't edit a foreign key in place; drop it and add it again:

ALTER TABLE comments DROP CONSTRAINT comments_post_id_fkey;
ALTER TABLE comments
  ADD CONSTRAINT comments_post_id_fkey
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE;

Find the constraint name in the error message, or with \d comments in psql. Do it in a migration so every environment matches. (Database migrations explained)

In ORMs

Prisma: @relation(fields: [postId], references: [id], onDelete: Cascade). Drizzle: .references(() => posts.id, { onDelete: 'cascade' }).

Performance note

Postgres doesn't automatically index foreign key columns. Without an index on comments.post_id, every delete from posts has to scan the whole comments table to find children. Add one. (Database indexes)

EasySpawn runs your Postgres database with daily backups and a separate development copy, so a cascade that goes further than expected is recoverable. See how it works or join the waitlist.

Related: Primary Key vs Foreign Key · Database Relationships Explained · Soft Deletes and Audit Logs · How to Design Your First Database

Keep reading