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
SET NULL: keep the child, drop the link
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:
- Use CASCADE only for true "part-of" relationships.
- Prefer soft deletes (a
deleted_atcolumn) for important data. (Soft deletes and audit logs) - Have tested backups. (Backups for beginners)
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
What Is UTF-8? Character Encoding and Garbled Text (Mojibake) Explained
UTF-8 is the character encoding that lets computers store every language and emoji as bytes — and it's the default almost everywhere now. How encoding works, why you see garbled text like ’ or é, and how to fix it in HTML, databases, CSV files and code.
What Is Firebase? Google's App Backend Explained for Beginners
Firebase is Google's backend-as-a-service: a database, logins, file storage, hosting and serverless functions you use from your app without running a server. What each part does, how the free and pay-as-you-go plans work, how security rules protect your data, and when Firebase is (or isn't) the right fit.