Prevent Accidental Cascading Deletes: Soft Deletes & Constraints

Updated 14 November 2026 · Standard formula, shown below

This guide shows you how to prevent accidental data loss from cascading deletes in relational databases by combining soft deletes (a flag that marks a row as inactive rather than removing it) with hard database constraints (foreign keys with restricted actions). You’ll learn how to design your schema so that even if a delete statement is issued, it either fails safely or only marks the record, never silently wiping out dependent rows. This approach is essential for any application where historical data, audit trails, or referential integrity matter.

What You’ll Need

A relational database that supports foreign key constraints (e.g., PostgreSQL, MySQL, SQL Server, SQLite with foreign keys enabled).
Access to run DDL (CREATE TABLE, ALTER TABLE) and DML (UPDATE, DELETE) statements.
Understanding of basic SQL and your database’s specific syntax for constraints and triggers (if used).
Access to your application’s data access layer (ORM or raw SQL) to modify delete logic.

Step-by-Step

Add a soft delete column to every table that should never be hard-deleted. Typically a boolean column named deleted or is_deleted, or a timestamp deleted_at (NULL means active, non-NULL means deleted). For example: ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;
Create a unique index that includes the soft delete flag to prevent duplicate active rows. For a table with a natural key like email, use a partial unique index: CREATE UNIQUE INDEX idx_users_email_active ON users(email) WHERE deleted_at IS NULL; This ensures only one active user per email, while allowing multiple deleted rows with the same email.
Replace all hard DELETE statements in your application with UPDATE statements that set the soft delete flag. For example, instead of DELETE FROM users WHERE id = 123; use UPDATE users SET deleted_at = NOW() WHERE id = 123; This is the core of soft deletes.
Modify all queries that read data to filter out soft-deleted rows. Add WHERE deleted_at IS NULL to every SELECT, UPDATE, and DELETE that should only affect active records. For example: SELECT * FROM orders WHERE user_id = 123 AND deleted_at IS NULL;
Define foreign key constraints with ON DELETE RESTRICT (or NO ACTION) instead of CASCADE. This is the critical safety net. For each child table that references a parent, use: FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT; If a hard delete is ever attempted on the parent, the database will reject it if any child rows exist.
For tables that should be hard-deletable, but have children, use ON DELETE SET NULL or SET DEFAULT only if the column is nullable and you accept orphaned children. But for most business data, prefer RESTRICT and rely on soft deletes.
Implement a “hard delete” function in your application that performs a cascading soft delete manually. When you truly need to remove a record and its children (e.g., for GDPR), write a transaction that first soft-deletes all children, then soft-deletes the parent, and only then performs hard deletes if absolutely necessary. Never rely on the database to cascade hard deletes.
Add a trigger (optional but recommended) that prevents accidental hard deletes on soft-deletable tables. For example, in PostgreSQL: CREATE FUNCTION prevent_hard_delete() RETURNS trigger AS $$ BEGIN RAISE EXCEPTION 'Hard delete not allowed on this table'; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_no_delete BEFORE DELETE ON users FOR EACH ROW EXECUTE FUNCTION prevent_hard_delete(); This blocks any direct DELETE statement, forcing the application to use soft deletes.
Test your setup thoroughly. Write test cases that attempt to delete a parent with children, both via the application and directly via SQL, and verify that the delete is rejected or soft-deleted as expected.
Document your soft delete policy. Ensure every developer knows that hard deletes are forbidden on these tables and that they must use the provided soft delete methods.

Common Mistakes

Using ON DELETE CASCADE without thinking. This is the #1 cause of accidental data loss. Even if you use soft deletes, a single hard delete on a parent can still cascade to children if the constraint is CASCADE.
Forgetting to filter soft-deleted rows in queries. This leads to duplicate records or showing deleted data to users.
Not updating all application code paths. If you have multiple services or scripts that issue DELETE statements, they must all be changed.
Using a unique index on a column that allows NULLs incorrectly. In many databases, NULLs are considered distinct, so you need a partial index (as shown) to enforce uniqueness only on active rows.
Assuming soft deletes are a substitute for proper constraints. They are complementary; constraints provide the safety net.

Tips

Use a timestamp (deleted_at) instead of a boolean so you know when the deletion happened, which is useful for audits.
Consider a global “deleted_by” column to track who performed the soft delete.
For large tables, index the deleted_at column to keep queries fast.
In ORMs like Django or Rails, use built-in soft delete packages or mixins to centralize the logic.
Set ON DELETE RESTRICT as the default for all new foreign keys, and only change it deliberately.

Troubleshooting

If a hard delete is still possible, check your triggers. Ensure the trigger is created on the correct table and that it raises an exception. Also check if your database user has privileges to bypass triggers (e.g., superuser in PostgreSQL).
If you get a foreign key violation when trying to soft delete a parent, it’s because the child rows are still active. You must soft delete the children first, or change the foreign key to ON DELETE SET NULL if that makes sense.
If your unique index fails because of existing duplicates, clean up the data first. Run a query to find duplicates and decide which ones to mark as deleted.
If your application still shows deleted records, check that all SELECT queries include the deleted_at IS NULL filter. A common oversight is in JOIN conditions.
If you’re using an ORM and it still generates DELETE statements, you may need to override the default delete method. Look for a delete() method on the model and replace it with an update.

Frequently Asked Questions

Can I use soft deletes on every table?

Yes, but it adds complexity. For lookup tables that are never referenced by foreign keys, hard deletes are fine. For anything with dependencies, soft deletes are safer.

What if I need to hard delete a record for legal reasons?

Implement a special administrative function that performs a hard delete only after verifying that all dependent data has been handled (e.g., anonymized or exported). Use a transaction and log the action.

Does ON DELETE RESTRICT block the delete if there are no children?

No, it only blocks if child rows exist. If no child references the parent, the delete succeeds.

Can I use ON DELETE NO ACTION instead of RESTRICT?

Yes, they are nearly identical. The difference is in timing: NO ACTION checks at the end of the transaction, while RESTRICT checks immediately. For most cases, either works.

How do I handle cascading soft deletes?

Write a stored procedure or application code that updates all child tables to set their soft delete flag, then updates the parent. Do this in a transaction to ensure consistency.

Is it enough to just use soft deletes without constraints?

No. Without constraints, a developer could accidentally issue a hard delete and lose data. Constraints are the last line of defense.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.