A deactivated phone number re-registers, but the unique index still says duplicate—is soft delete not cleaning up properly?

After a soft delete, a unique index conflict usually does not mean the index was built incorrectly; it means the deleted row still occupies the unique value. Based on 2026 project delivery experience, the goal is not to simply append is_deleted to the unique index, but to make undeleted records remain unique while deleted records can repeat. Common approaches include using a deletion timestamp or deletion version number with a unique index, or using generated columns, partial indexes, or physically deleting from the main table and archiving to a history table.

Why does soft delete collide with a unique index?

A unique index constrains the entire table; it does not care whether a record has been soft-deleted. As long as the row remains, the unique field value is still occupied. If a phone number has a unique index and after the user deactivates the account only deleted is set to 1, re-registering the same phone number will raise Duplicate entry on insert. Soft delete wants to retain data, while the unique index wants to prevent duplicates; their goals conflict here.

• A unique index covers the whole table: the delete flag is just an ordinary field and does not invalidate the unique value.
• Soft delete does not remove the row: the data is still there, so the unique value is still there.
• Inserting the same value again triggers a conflict: the database is doing its job under index rules.
• Conflicts cluster in reused fields: phone numbers, emails, ID numbers, order numbers, and similar fields are prone to collisions.

In delivery settings, this conflict is often discovered not during design, but when operations re-enable an old account, import historical data, or customer service manually creates an order. By then, changing the index often requires changing queries, import scripts, and reports as well.

Common approaches compared: start with modification cost and applicable conditions

Appending the delete flag directly to the unique index only solves uniqueness for undeleted records; it does not allow deleted records to repeat. The approaches below each have conditions; choose by database type and concurrent write intensity.

1. Deletion timestamp or deletion version + unique index: change the unique index to something like phone + deleted_at. When not deleted, deleted_at stores a fixed value; when deleted, write the current timestamp or an incrementing version number. Do not use NULL to represent not deleted; see the FAQ for why.
2. Generated column or partial index: PostgreSQL can evaluate partial indexes; MySQL can evaluate generated columns, where the generated column equals the unique value when not deleted and is NULL when deleted, then create a unique index on the generated column. Syntax depends on the database version; verify against official documentation before making changes.
3. Physical delete from the main table + history table archiving: on delete, move the entire row to a history table, physically delete it from the main table, and keep the unique index as is. Suitable when the unique value will not be reused after deletion and more complex join queries are acceptable.
4. Application-layer duplicate check with locking: do not create a unique index; query to check whether the value exists and lock during insert. Under concurrency it is easy to miss, so it is usually only auxiliary and not recommended as the sole safeguard.

Approach comparison: experience ranges for cost and risk

• Deletion timestamp/version: changes the index and write logic, experience range about 0.5–2 person-days; a version number avoids same-second conflicts better than a second-level timestamp.
• Generated column or partial index: typical cost 0.5–3 person-days, depending on database version and team familiarity; verify syntax against official documentation.
• Physical delete from main table + history table: changes the delete entry, query entry, and reports, with an experience range typically 3–10 person-days; the more entries, the closer to the upper bound.
• Application-layer duplicate check with locking: low modification cost, but high risk of missed detection under concurrency, so it is usually not used alone.

Based on common 2026 delivery habits, when a team uses MySQL and does not want to introduce a history table, a deletion timestamp or version combined with a unique index is a relatively low-cost modification; when deletion volume is large and historical queries are rare, a history table is actually cleaner.

How do you decide which one to use?

The decision order is not to choose a technology first, but to ask the business first: will this unique field be reused after the record is deleted? If it will be reused, the unique value must be released; if it will not be reused, the original unique index can remain and only soft delete is needed, but queries must add a filter.

1. Will the unique field be reused?: phone numbers, emails, and ID numbers may be re-registered after deactivation, so they should be released; order numbers and contract numbers are usually not reused, so the original index can remain.
2. Deletion frequency: with dozens to hundreds of deletions per day, a deletion timestamp is usually sufficient; with up to tens of thousands per day, evaluate a history table or version number to reduce the chance of same-second conflicts.
3. Database type: PostgreSQL can evaluate partial indexes; MySQL does not have partial indexes, so it usually chooses among timestamps, generated columns, and history tables.
4. Can a history table be accepted?: a history table complicates join queries, reports, and foreign key relationships, so it suits scenarios with few query entries and rare post-deletion queries.
5. Concurrent write intensity: under strong concurrency, application-layer duplicate checking is unreliable; the database unique constraint should be the safeguard.

Experience range: when the unique field will be reused and daily deletions are in the dozens to hundreds, a deletion timestamp with a unique index is a common approach; when daily deletions reach tens of thousands, prioritize a history table or deletion version number to reduce the chance of same-second conflicts.

Delivery field experience: the rework cost of adding soft delete late

An admin management system had tight budget and schedule; soft delete was added temporarily before launch, and the unique index had already been created. The first version appended is_deleted to the unique index. As a result, when operations re-enabled an old phone number, deleting the second record with the same value caused a conflict. The rework changed it to a deletion timestamp, which also required adjusting query conditions, import scripts, and reports; the cost was typically a 1–3 day delay. The constraints were many entries and no one willing to change reports; the approach was to first list the unique indexes and delete entries, then uniformly change writes and queries; the result was more stable than continuing to patch, but it also shows that soft delete should have its deletion strategy decided during design.

Applicable and non-applicable boundaries

Soft delete suits business tables that need audit traceability, recovery from accidental deletion, unbroken related data, and industry rules requiring records to be retained, such as user master tables, order master tables, and fund flow tables. Once these tables are physically deleted, later account checks and customer service traceability become much harder.

Non-applicable cases should also be stated clearly: temporary tables, log tables, cache data, and import staging tables are usually easier to physically delete; when a unique field will never be reused, the original unique index can remain with only soft delete, but queries still need filtering. Soft delete is not the default option; if it is only for the appearance of safety, physical delete plus backups is often simpler.

FAQ

After soft delete, a unique index conflict occurs. Can is_deleted be added to the unique index?

No. is_deleted only has 0 and 1, so deleting a second record with the same value will still conflict. Use a deletion timestamp, version number, or history table to release the unique value.

Why aren't multiple NULLs in a MySQL unique index considered a conflict?

MySQL treats NULL as not equal to NULL, so a unique index allows multiple NULLs. If NULL is used to represent not deleted, undeleted records with the same unique value can also coexist, and the constraint fails.

Which is easier, a deletion timestamp or a deletion version number?

A timestamp is intuitive, but deleting two records with the same value in the same second can still conflict; a version number or millisecond timestamp is more stable. Typical range: use a timestamp for dozens to hundreds of daily deletions, and prioritize a version number or history table for tens of thousands.

Does physical delete from the main table plus a history table affect queries?

Yes. The history table does not participate in main table joins, so reports and join queries must be handled separately, and modification cost is higher. It suits scenarios where post-deletion queries are rare and the unique value is not reused.

After soft delete, do all queries need a delete filter?

Usually yes. Miss one place, and deleted data may reappear in lists. A common practice is to add the condition uniformly through views, global filters, or ORM interception.

If you are about to add soft delete to an existing table, do not change the code first. Confirm which unique indexes exist, whether unique fields will be reused after deletion, and how many query entries there are. Based on 2026 delivery habits, write the deletion strategy, unique index form, and query filtering method into a checklist before touching the database. Soft delete suits business tables that need auditing and recovery; temporary tables and log tables do not need to follow the trend.