Soft Delete in MySQL: Data Deleted, but Business Still Has a Way Back

The delete button does not always mean that data must be completely removed from the database. In online store applications, membership systems, helpdesks, or document management, data that seems unused today may be needed again in a few weeks.

This is where soft delete comes in handy. Instead of executing DELETE and permanently removing rows, the application marks the data as inactive—usually with a deleted_at column. The data no longer appears in the normal view, but is still available for recovery, auditing, or planned permanent deletion processes.

What is soft delete?

Soft delete is a data deletion pattern that changes the status or deletion time, rather than directly removing rows from the table.

Example of a simple table structure:

CREATE TABLE customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL,
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

If deleted_at is NULL, the data is considered still active. If it contains a specific time, the data is considered logically deleted.

This approach differs from adding a column like is_deleted with values 0 or 1. Both can be used, but deleted_at usually provides additional information: when the data was deleted and who knows, that information may be useful when investigating issues.

Why not just use DELETE?

Permanently deleting data is indeed simpler at first. However, the consequences can be troublesome.

• Data is difficult to recover when a user deletes it by mistake.
• Transaction history may lose references to old data.
• Support teams cannot check the condition before the data is lost.
• Auditing becomes more difficult because there is no trace that the data ever existed.
• Relationships between tables can be broken if the parent data is deleted first.

For example, a customer deletes their account, but old orders and invoices still need to be retained. If the customer row is permanently deleted, the application may lose the name or identity needed to display the transaction history.

Soft delete does not solve all problems, but it provides a buffer between the decision to “hide from the application” and “destroy from storage.” This buffer is important when mistakes can still be corrected.

Basic implementation in PHP and MySQL

To logically delete data, the application simply updates the timestamp column:

UPDATE customers
SET deleted_at = NOW(), updated_at = NOW()
WHERE id = :id AND deleted_at IS NULL;

The query to display active data must always include a filter:

SELECT id, name, email
FROM customers
WHERE deleted_at IS NULL
ORDER BY created_at DESC;

With PDO, parameters still need to be used to ensure that user values are not directly concatenated into the SQL string:

$stmt = $pdo->prepare(
    'UPDATE customers
     SET deleted_at = NOW(), updated_at = NOW()
     WHERE id = :id AND deleted_at IS NULL'
);

$stmt->execute(['id' => $customerId]);

A common mistake is only applying the filter on one page. The list page may be correct, but search endpoints, admin reports, or APIs may still display deleted data. Therefore, the rule of “only active data” should be consistently enforced at the data access layer, rather than relying on each programmer's memory.

Don't forget the restore feature

Soft delete is most useful if the application provides a way to recover data. The recovery query is simple:

UPDATE customers
SET deleted_at = NULL, updated_at = NOW()
WHERE id = :id AND deleted_at IS NOT NULL;

However, restoring is not just a button. The application needs to consider potential conflicts. For example, the email address of a deleted customer may then be used by a new account. When the old account is restored, the unique constraint on the email column could cause the process to fail.

Therefore, before restoring, the system should check whether the required data is still available and whether business rules are still met. If not, provide a clear reason to the admin, rather than a hard-to-understand database error message.

Unique issues: email, username, and product codes

Soft delete often raises questions about unique constraints. Can the email from deleted data be reused? The answer depends on the application's needs.

If the email must remain unique even among deleted accounts, a regular unique index can be maintained. However, if the email can be used by a new account after the old account is deleted, the table design must support that rule.

One approach is to move old data to an archive table. Another approach is to use a special column that distinguishes between active and deleted data, but its implementation needs to be adjusted according to the database and the version of MySQL used. Do not disable constraints just to eliminate errors, as this can lead to duplicate data that is difficult to clean up.

Soft delete is not a substitute for retention policies

Data marked as deleted still takes up space and may still contain personal information. Therefore, soft delete needs to be complemented by a retention policy: how long data is stored, who can recover it, and when data is truly destroyed.

Example of a periodic cleanup process:

DELETE FROM customers
WHERE deleted_at IS NOT NULL
  AND deleted_at < NOW() - INTERVAL 2 YEAR;

Queries like this should not be run carelessly. Ensure that there is a backup, relationship checks, and approvals as per business needs. For sensitive data, permanent deletion should also be considered from the perspective of privacy policies and data storage obligations.

Checklist before implementing soft delete

• Use consistent column names, such as deleted_at.
• Add active data filters to all list, search, report, and API queries.
• Provide a restore mechanism that is only accessible by authorized roles.
• Record who deleted and restored data if auditing is needed.
• Check unique rules for emails, usernames, invoice numbers, and other codes.
• Establish a permanent cleanup schedule for data that has been deleted for a long time.
• Test inter-table relationships to ensure logical deletions do not result in orphaned data.

What does this mean for us?

Soft delete is not a rule that all data must be stored forever. It is a way to design applications so that mistakes that can still be corrected do not immediately turn into permanent data loss.

Use soft delete for data that has historical value, is connected to transactions, or may need to be recovered. For temporary data that is unimportant and has no relationships, permanent deletion may make more sense. The best decision is not the one easiest to write in a single query, but the one most appropriate to the risks and data lifecycle in the application.

– Rio Yotto @rioyotto