✏️ Explanatory Question
Level: Hard — Widely used but full of subtle bugs; the UNIQUE-constraint clash and forgotten filter are classic production issues.
deleted_at column (soft delete). Then two bugs appear: (1) a "deleted" user's email can't be reused for a new signup, and (2) a report accidentally counts deleted users. Explain both and how to do soft delete correctly.
DELETE FROM users — the row is physically gone. Simple, but unrecoverable and loses history.deleted_at timestamp (or is_deleted flag). The row stays; queries filter it out. Enables restore, audit, and referential history.CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
name VARCHAR(100),
deleted_at TIMESTAMP NULL DEFAULT NULL -- NULL = active, timestamp = deleted
);| id | deleted_at | State | |
|---|---|---|---|
| 1 | rumman@x.com | NULL | Active |
| 2 | krushna@x.com | 2026-07-01 10:00 | Soft-deleted |
You put UNIQUE(email) so no two users share an email. But Krushna is soft-deleted (still physically in the table). When someone tries to sign up again with krushna@x.com, the UNIQUE constraint rejects it — even though that user is "deleted." A dead row is blocking a live signup.
-- PROBLEM: plain UNIQUE blocks reusing a soft-deleted email
-- ALTER TABLE users ADD UNIQUE (email); -- too strict
-- FIX (MySQL 8.0+): functional index that only enforces uniqueness on ACTIVE rows
-- Deleted rows get NULL in the expression, and NULLs don't collide in UNIQUE
ALTER TABLE users
ADD UNIQUE INDEX uq_active_email (
(CASE WHEN deleted_at IS NULL THEN email END)
);
-- Now: only ONE active row per email, but any number of deleted ones
-- FIX (portable): include deleted_at in the unique key
-- ALTER TABLE users ADD UNIQUE (email, deleted_at);
-- Caveat: multiple NULLs are allowed, so this permits repeated ACTIVE emails
-- unless you use a sentinel value instead of NULL for deleted_at.Every query must add WHERE deleted_at IS NULL. Forget it once, and deleted rows leak into reports, lists, and counts. This is the #1 soft-delete bug — it's easy to miss and hard to spot.
-- WRONG: forgets the filter -> counts deleted users
SELECT COUNT(*) FROM users; -- includes Krushna!
-- RIGHT: always filter
SELECT COUNT(*) FROM users WHERE deleted_at IS NULL;
-- SAFER: expose a view that hides deleted rows by default
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;
-- App queries the view; the filter can never be forgotten
SELECT COUNT(*) FROM active_users;
-- The delete & restore operations
UPDATE users SET deleted_at = NOW() WHERE id = 5; -- soft delete
UPDATE users SET deleted_at = NULL WHERE id = 5; -- restore| Pro | Con |
|---|---|
| Recoverable / restore | Every query needs the filter |
| Keeps audit history | UNIQUE constraints get tricky |
| Preserves FK references | Table grows; indexes bloat |
| No cascade-delete data loss | Must periodically purge old rows |
deleted_at (not a boolean — you get the deletion time for free), enforce active-only partial unique indexes, access data through views to avoid forgotten filters, and add a purge job to hard-delete very old soft-deleted rows so the table doesn't grow forever.
deleted_at IS NULL to your indexes (or use partial indexes) so active-row lookups stay fast, (2) partition by deleted/active, or (3) periodically move old soft-deleted rows to an archive table, keeping the hot table lean.