If you've been using social media lately, such as Facebook or Instagram, head to the account settings and look for the delete account option. You'll notice that these platforms usually offer you two choices: either deactivate your account (make it invisible to everybody else) or permanently delete it after 30 days. During that period, you can recover your account at any time.

This is soft delete in action.

What is soft delete?

Soft delete is a mechanism that marks a database record as deleted instead of physically removing it from the database. The record becomes invisible to the application, but the data is still preserved and can usually be recovered later.

Implementing Soft Delete in SQL

To implement soft delete in SQL, you add a tracking column (usually a nullable timestamp, deleted_at, and/or a boolean flag, is_deleted) to your table to mark records as deleted without physically removing them from the database.

Here is the complete step-by-step implementation.

1. Modify the Schema

Identify the table that you want to apply soft delete to, then add an is_deleted boolean column to indicate whether a record is deleted or not. It's also highly recommended to add a deleted_at timestamp column to register the exact date and time of the deletion. This is crucial for audit trails.

ALTER TABLE customers
ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
ADD COLUMN deleted_at TIMESTAMP NULL;

2. Update Instead of Delete

When a user triggers a deletion, replace your standard DELETE query with an UPDATE query that updates your state-tracking columns.

UPDATE customers
SET
    is_deleted = TRUE,
    deleted_at = CURRENT_TIMESTAMP
WHERE id = 101;

3. Querying

Every query that reads data from the database must now actively exclude soft-deleted records by checking for NULL values.

SELECT
    id,
    first_name,
    last_name,
    email
FROM customers
WHERE deleted_at IS NULL;

Why Applications Use Soft Delete

Recovery

One of the biggest advantages of soft delete is data recovery. Since records remain stored in the database, accidentally deleted data can usually be restored with a simple update operation instead of relying on backups.

Auditing

Soft delete preserves information about deleted records, making it easier to track changes over time. Combined with a deleted_at timestamp, it also provides a complete history of when a record was removed, which is essential for audit trails.

Historical Analysis

Deleted records can still provide valuable insights. Applications can use this historical data to analyze user behavior, measure customer retention, identify trends, and generate more accurate reports.

Data Integrity

Physically deleting a record can break relationships between tables and create orphaned records. Soft delete preserves the data and maintains the relationships between tables, reducing the risk of data integrity issues.

Conclusion

Soft delete is not an afterthought that you introduce in the middle of a project. It's an essential mechanism that should be considered during the early stages of a database schema design.

Before designing your tables, identify the data that should remain available after deletion and determine whether it needs to be recovered, audited, analyzed, or preserved to maintain data integrity.

Like every database design decision, soft delete comes with trade-offs. Not every table requires it, but for many applications, implementing it from the beginning can prevent data loss and make your system more reliable in the long term.

If you want to learn more about planning and structuring your database, check out our complete database schema design guide.