MySQL Soft Delete: deleted_at Column, Queries and Pitfalls

Soft delete means that a “deleted” row stays in the table, with a mark that says it is deleted. Applications hide it, and it can be restored.

query.sql

SQL

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    deleted_at DATETIME NULL DEFAULT NULL
);
INSERT INTO posts (title) VALUES ('Hello world'), ('Draft idea'), ('Old news');

UPDATE posts SET deleted_at = '2026-07-01 12:00:00' WHERE title = 'Old news';

A NULL deleted_at means “alive”. A date means “deleted at that moment”.

Queries must filter

query.sql

SQL

SELECT id, title FROM posts WHERE deleted_at IS NULL ORDER BY id;

Output

OUTPUT

+----+-------------+
| id | title       |
+----+-------------+
|  1 | Hello world |
|  2 | Draft idea  |
+----+-------------+
2 rows in set

Every query on this table now needs AND deleted_at IS NULL. Forget it once and deleted rows leak into pages, reports and counts. A view can hide the filter:

query.sql

SQL

1
2

CREATE VIEW live_posts AS SELECT id, title FROM posts WHERE deleted_at IS NULL;
SELECT * FROM live_posts ORDER BY id;

Output

OUTPUT

+----+-------------+
| id | title       |
+----+-------------+
|  1 | Hello world |
|  2 | Draft idea  |
+----+-------------+
2 rows in set

Restore

query.sql

SQL

1
2

UPDATE posts SET deleted_at = NULL WHERE title = 'Old news';
SELECT COUNT(*) AS live_posts FROM live_posts;

Output

OUTPUT

+------------+
| live_posts |
+------------+
|          3 |
+------------+
1 row in set

The problems

1. Unique keys. If email is UNIQUE, a soft-deleted user still holds the email, and a new account with the same email is rejected:

query.sql

SQL

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(50) NOT NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_email (email)
);
INSERT INTO users (email, deleted_at) VALUES ('a@example.com', '2026-06-01 00:00:00');
INSERT INTO users (email) VALUES ('a@example.com');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry 'a@example.com' for key 'users.uq_email'

A unique index on (email, deleted_at) looks like a fix, but it does not work: a live row has deleted_at = NULL, MySQL allows several rows with NULL in a unique index, and so two live rows with the same email would both be accepted. The robust MySQL pattern is a generated column that holds the email for live rows and NULL for deleted ones:

query.sql

SQL

CREATE TABLE users2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(50) NOT NULL,
    deleted_at DATETIME NULL,
    live_email VARCHAR(50) AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL,
    UNIQUE KEY uq_live_email (live_email)
);
INSERT INTO users2 (email, deleted_at) VALUES ('a@example.com', '2026-06-01 00:00:00');
INSERT INTO users2 (email) VALUES ('a@example.com');
INSERT INTO users2 (email) VALUES ('a@example.com');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry 'a@example.com' for key 'users2.uq_live_email'

live_email is the email for live rows and NULL for deleted ones. A unique index ignores NULL, so the deleted copy does not block a new account, but two live rows with the same email are still refused (the third insert fails).

2. Foreign keys. A soft-deleted parent still has children, and nothing stops them from pointing to a “deleted” row.

3. Growth. Deleted rows are never removed, so tables and indexes grow, and every query pays for them.

4. Privacy law. If a user asks you to delete their data (GDPR and similar laws), a soft delete does not meet the request. The data must be really removed or anonymised.

5. Cascades. Soft-deleting a parent does not soft-delete its children unless your code does it.

When soft delete is a good idea

Users may undo a deletion (a trash bin).
You must keep a record for audit or legal reasons.
Other records must still show what was deleted (an old order that refers to a removed product).

The alternative: archive tables

Move the row to an archive table and really delete it from the live table, in one transaction:

query.sql

SQL

CREATE TABLE posts_archive LIKE posts;

START TRANSACTION;
INSERT INTO posts_archive SELECT * FROM posts WHERE title = 'Draft idea';
DELETE FROM posts WHERE title = 'Draft idea';
COMMIT;

SELECT (SELECT COUNT(*) FROM posts) AS live_rows, (SELECT COUNT(*) FROM posts_archive) AS archived_rows;

Output

OUTPUT

+-----------+---------------+
| live_rows | archived_rows |
+-----------+---------------+
|         2 |             1 |
+-----------+---------------+
1 row in set

The live table stays small and fast, queries need no extra filter and unique keys work. The cost is a second table to maintain.

Choosing

Need	Choice
Trash bin with restore	Soft delete + a view or a global filter in your framework
Audit trail	A separate audit/history table
Keep the live table fast and clean	Archive table
Legal erasure	Real delete (or anonymise)

lightbulb

Pick one approach per table

Mixing soft delete, hard delete and archiving on the same table is a good recipe for confusion. Decide, and write it down.