Logical Delete vs Unique Index: The Duplicate Entry Trap and the Generated Column Fix

The Problem: Logical Deletion Meets Unique Index

A project with code JAVA001 is created, then logically deleted (setting deleted=1). When attempting to re-create the same code, MySQL throws Duplicate entry 'JAVA001' for key 'uk_project_code' because the unique index on project_code still sees the deleted row.

First Attempt: Composite Unique Index (code, deleted)

Changing the index to UNIQUE KEY uk_project_code (project_code, deleted) seems to fix it. The first cycle works:

Active: (JAVA001, 0) Deleted: (JAVA001, 1) Re-create: new row (JAVA001, 0) — no conflict because (JAVA001, 0) is free.

But a second delete fails: updating the new row to deleted=1 tries to create another (JAVA001, 1), which already exists from the first deletion. The composite index only supports one delete-create cycle.

Root Cause: Wrong Constraint Semantics

Only "currently active data" must have unique project_code; historical deleted data should not participate in uniqueness.

The business rule is: deleted = 0 → project_code unique; deleted = 1 → don't care about duplicates. The composite index enforces uniqueness for both deleted=0 and deleted=1, causing deleted rows to conflict with each other.

Solution: Generated Column + NULL in Unique Index

MySQL's unique index allows multiple NULL values. Create a virtual generated column that holds the code when active and NULL when deleted:

ALTER TABLE project
ADD COLUMN active_project_code VARCHAR(64)
GENERATED ALWAYS AS (
  CASE WHEN deleted = 0 THEN project_code ELSE NULL END
) VIRTUAL;

Then create the unique index on that column:

CREATE UNIQUE INDEX uk_project_active_code ON project(active_project_code);

Behavior:

Active row: project_code=JAVA001, deleted=0, active_project_code=JAVA001 Deleted row: project_code=JAVA001, deleted=1, active_project_code=NULL Multiple deleted rows all have active_project_code=NULL — allowed because unique index permits many NULLs.

Only one active row with active_project_code=JAVA001 can exist at any time.

This directly expresses the business rule: uniqueness only for active data.

Multi-Tenant Extension

For SaaS where uniqueness is per tenant:

ALTER TABLE project
ADD COLUMN active_project_code VARCHAR(64)
GENERATED ALWAYS AS (
  CASE WHEN deleted = 0 THEN project_code ELSE NULL END
) VIRTUAL;

CREATE UNIQUE INDEX uk_tenant_active_project_code
ON project(tenant_id, active_project_code);

Now tenant_id + active project_code must be unique, not tenant_id + project_code + deleted.

Application-Level Check Is Not Enough

Service-layer existence checks ( existsByTenantIdAndProjectCodeAndDeletedFalse) improve user experience but cannot prevent race conditions. Two concurrent requests can both see "not exists" and then both insert. The database unique index is the final guard.

Recommended two-layer approach:

Service check: friendly error message early.

Database unique index: absolute consistency under concurrency.

Always catch DataIntegrityViolationException on save and translate to business exception.

Precise Exception Handling

Don't map all DataIntegrityViolationException to "code exists". The exception may come from other unique indexes, foreign keys, or NOT NULL constraints. Inspect the constraint name (e.g., uk_tenant_active_project_code) before converting. Use meaningful constraint names like uk_project_tenant_active_code for easier debugging.

Alternative Approaches and Their Trade-offs

Modify the Code on Delete

Append suffix: username = zhangsan_deleted_10086. Simple but mutates historical business data, breaking audit trails and external references.

Use deleted_at Timestamp

Set deleted_at to timestamp on delete, keep NULL when active. Unique index on (tenant_id, project_code, deleted_at). Problem: active rows all have deleted_at=NULL, and MySQL allows multiple NULLs in a unique index, so active rows would NOT be forced unique. This loses the required protection.

Data Migration Safety

Before creating the new generated-column index, audit existing active data for duplicates:

SELECT tenant_id, project_code, COUNT(*) AS cnt
FROM project
WHERE deleted = 0
GROUP BY tenant_id, project_code
HAVING COUNT(*) > 1;

Resolve any duplicates manually before adding the constraint. The index only prevents future conflicts; it does not fix existing ones.

Testing the Full Lifecycle

Write integration tests that exercise multiple delete-create cycles:

@Test
void should_allow_recreate_after_multiple_deletes() {
  Long first = createProject("JAVA001");
  deleteProject(first);
  Long second = createProject("JAVA001");
  deleteProject(second);
  Long third = createProject("JAVA001");
  assertThat(third).isNotNull();
}

This verifies the data model truly supports logical deletion with code reuse.

When NOT to Allow Code Reuse

Some business codes (contract numbers, order IDs) must never be reused, even after deletion. In that case, a simple UNIQUE(business_no) is correct. Always align the database constraint with the actual business rule, not with a default pattern.

Design Checklist for Logical Delete Tables

Can the code be reused after deletion? If no → UNIQUE(code).

If yes, is the rule "only active data unique"? If yes → generated column + unique index on active value.

Don't mechanically add deleted to a composite index.

The core insight: Let deleted rows exit the uniqueness constraint by making their index value NULL, rather than trying to make deleted rows unique among themselves.