Soft Delete in MySQL: How to Delete Data without Losing Trace

The delete button is usually the simplest part of an application. Users press the button, the server executes DELETE, and the data disappears. Problems arise when the deleted data is still needed: orders need to be checked, accounts must be restored, or the team wants to know who changed the record.

This is where soft delete comes in handy. Instead of actually removing a row from the database, the application marks it as inactive. The data remains stored but is no longer displayed in the normal list.

What is soft delete?

Soft delete is a data deletion pattern that adds a marker, usually a column like deleted_at, is_deleted, or status. When a user deletes data, the application does not perform a permanent deletion. The application simply changes the value of that marker.

A simple example on the users table:

ALTER TABLE users
ADD COLUMN deleted_at DATETIME NULL;

Active data has a deleted_at value of NULL. When deleted, that column is filled with the deletion time:

UPDATE users
SET deleted_at = NOW()
WHERE id = 42;

The query to display active users then needs to include an additional condition:

SELECT id, name, email
FROM users
WHERE deleted_at IS NULL;

Physically, the row still exists. From a business perspective, the data is considered inactive.

Why not just use DELETE?

Permanently deleting still has its place. However, for many applications, data should not just disappear. There are several practical reasons to use soft delete.

• User error recovery. Data that is accidentally deleted can still be restored.
• Transaction history. Orders, invoices, or support tickets may need to remain available for review.
• Data relationships. Permanently deleting a user can cause issues with other data that still references it.
• Audit needs. Teams can know when a piece of data was deactivated, especially if a deleted_by column is added.

However, soft delete does not mean the database is automatically secure. Marked deleted data still needs to be protected, and access rules must be carefully established.

Common mistake: forgetting to filter data

A classic soft delete problem occurs when a query forgets to add WHERE deleted_at IS NULL. As a result, data that should be inactive reappears on user pages, reports, or search results.

This mistake often happens because filters are written repeatedly in many places. One way to reduce this is to create a function or data access layer that always applies the default filter.

A simple example in PHP using PDO:

$sql = 'SELECT id, name, email
        FROM users
        WHERE deleted_at IS NULL
        ORDER BY name ASC';

$statement = $pdo->prepare($sql);
$statement->execute();
$users = $statement->fetchAll(PDO::FETCH_ASSOC);

For larger applications, this pattern can be placed in a repository, model, or query builder. The goal is not just to reduce code writing but to prevent a single endpoint from displaying data that should be hidden.

Differentiate between active, deleted, and permanently deleted data

Do not assume soft delete only has two states: present and absent. In real applications, there are usually at least three conditions:

1. Active: can be used in normal processes.
2. Temporarily deleted: does not appear in public views but can still be restored.
3. Permanently deleted: completely discarded after passing certain rules.

This distinction is important for designing admin pages. Regular users may only see active data, while admins can view the trash or archive. Data in the trash should also have a time limit, for example, permanently deleted after exceeding a specified retention period.

Use deleted_at, not just is_deleted

Boolean columns like is_deleted are easy to understand, but the information is limited. A TRUE value only indicates that the data has been marked. We do not know when that change occurred.

The deleted_at column stores the deletion time and is more useful for audits, recovery, and data cleanup tasks. If needed, also add:

• deleted_by to store the identity of the user who performed the action.
• delete_reason to record the reason for deletion.
• restored_at to note when the data was restored.
• restored_by to record who restored the data.

Not all applications require all these columns. Choose based on audit needs and business risks, not just to add as many columns as possible.

Pay attention to relationships and unique data

Soft delete becomes more complicated when tables have relationships. For example, a customer's address is marked as deleted, but old orders still use it. Should that address be removed from the admin view? Should orders still display the old address?

The answer depends on the context. Transaction data usually needs to maintain its historical condition. Therefore, do not always fetch relational data with active filters blindly. For invoices or old orders, information that has become part of the transaction may need to be copied as a snapshot.

Another issue arises with unique columns, such as email. If a user with a certain email is temporarily deleted, can a new user use the same email? MySQL does not automatically understand that business rule. The team needs to determine whether the email in deleted data is still considered in use or can be reused.

What you can do now

1. Identify which tables truly need recovery or audit.
2. Use deleted_at DATETIME NULL as the primary marker.
3. Create default query rules so that deleted data does not appear.
4. Provide an admin page to view and restore deleted data if necessary.
5. Determine when data can be permanently deleted.
6. Test relationships, searches, reports, and unique data validation after soft delete is implemented.

Soft delete is not a substitute for data policy

Soft delete is useful when applications need to maintain a trace and provide an opportunity to recover from errors. However, keeping all data forever is also not a good strategy. Databases can grow, searches become less efficient, and old data may no longer need to be stored.

A healthy approach is to combine soft delete with retention policies, access controls, backups, and measured permanent deletion processes. This way, the delete button not only works technically but also aligns with the operational needs of the application.

– Rio Yotto @rioyotto