Soft Delete in PHP and MySQL: Deleting Data without Losing Trace

The "Delete" button is often seen as a simple operation: run DELETE, and the data is gone. The problem arises when a user accidentally deletes an order, an admin needs to check account history, or data must be recovered without taking a backup of the entire database.

This is where soft delete comes in handy. Instead of permanently deleting rows, the application marks the data as inactive—usually with a deleted_at or is_deleted column. The data is still stored but is no longer displayed in normal operations.

This pattern is not a substitute for backups and is not a reason to keep all data forever. However, for many PHP and MySQL applications, soft delete can make deletions safer and the auditing process easier.

What is soft delete?

In a hard delete, the application completely removes the row from the table:

DELETE FROM users WHERE id = 42;

Once that command is successful, the data is no longer available through regular queries. Recovery is only possible if there is a backup, binlog, or other logging mechanism.

With soft delete, the application simply changes the marker:

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

The row remains, but the application query only needs to retrieve data with deleted_at IS NULL. A NULL value means the data is still active, while the date in that column indicates when the data was "deleted".

Why use deleted_at instead of just is_deleted?

The is_deleted column is indeed easier to read: its value can be 0 or 1. However, a date column is usually more informative because it stores the time of the event.

• Teams can know when the data was deactivated.
• The recovery process can log the age of deleted data.
• Admins can clean up old data based on the date.
• Auditing becomes easier when combined with the identity of the user who performed the action.

For more comprehensive needs, add columns like deleted_by or create a separate audit table. Do not mix all needs into one column if the application requires detailed change history.

Simple table structure

An example user table can be created as follows:

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(150) NOT NULL,
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_users_deleted_at (deleted_at)
);

The index on deleted_at can help when the table is large and almost all queries filter active data. However, the need for indexing should still be tested with real data and query patterns. Do not add indexes blindly, as each index also adds work during INSERT and UPDATE.

The most important rule: active queries must be consistent

The most common mistake in soft delete is not in the marking process, but in queries that forget to filter out deleted data.

Queries like this risk displaying accounts that should already be inactive:

SELECT id, name, email
FROM users
ORDER BY id DESC;

A safer version:

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

The problem is, in large applications, that filter must be added in many places: user lists, searches, reports, order relations, API endpoints, and admin pages. A single missed query could cause "deleted" data to reappear.

Therefore, make it a habit or clear abstraction. For example, separate functions for retrieving active data and specific functions for retrieving data including those that have been deleted.

function getActiveUsers(PDO $db): array
{
    $sql = 'SELECT id, name, email
            FROM users
            WHERE deleted_at IS NULL
            ORDER BY id DESC';

    return $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}

Function names like getActiveUsers help code readers understand the rules being applied. For frameworks, use global scope features or repositories if available, but still provide explicit paths for admin and audit needs.

Don’t forget unique rules on email or username

Soft delete often raises the question: if an account with the email user@example.com has been marked as deleted, can the user register again with the same email?

If the email column has a regular UNIQUE constraint, new registration will be denied because the old row still exists. This can be right or wrong, depending on business rules.

There are several options:

• Do not allow reuse. Suitable if account identity must remain unique forever.
• Allow reuse after deletion. The application needs to check active data and design a unique index that fits the database capabilities.
• Archive old identities. The email in the old row can be changed to an internal value, but this action must be understood as it affects auditing and recovery.

Do not make this decision solely from a technical standpoint. Ask what happens to orders, payment history, and account recovery processes.

The restore process should be treated as a critical operation

Data recovery is not just about changing deleted_at back to NULL. The application needs to check if the recovered data is still consistent.

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

Before executing this process, check for conflicts such as emails already used by other accounts, deleted organizations, or changed access rights. Recovery should be limited to certain roles and logged.

Soft delete is not a substitute for retention and backup

Keeping data with a deleted marker does not mean the data is safe forever. Rows that are never cleaned will continue to increase the size of the table, indexes, and backup time.

Establish a retention policy. For example, data that has been deleted for 90 days can be moved to an archive table or permanently deleted through a scheduled process. Before that, ensure recovery needs, auditing, and internal rules are met.

Backups are still necessary because soft delete does not protect against database corruption, mass errors, unauthorized access, or bugs that change many rows at once.

What you can do now

1. Add deleted_at DATETIME NULL to tables that require recovery.
2. Change all main queries to only retrieve data with deleted_at IS NULL.
3. Clearly define who can delete and restore data.
4. Test for conflicts on unique columns like email, slug, and document numbers.
5. Create logs for delete and restore actions.
6. Determine when old data is archived or permanently deleted.

Soft delete is most useful when treated as part of data design, not just adding a single column. With consistent query rules and clear retention policies, applications can allow for error correction without turning the database into an unlimited storage space.

– Rio Yotto @rioyotto