Enforcing Unique Emails with Soft Delete: Partial Indexes & Generated Columns

Why Duplicate Emails Appear

When a user registers, the application inserts email and password into the users table. If the submit button is clicked twice (or the request is retried), two identical rows are created because the database has no uniqueness guard on email. A later

SELECT * FROM users WHERE email = '[email protected]' LIMIT 1

returns an arbitrary row, causing inconsistent order history and user confusion.

Standard Unique Constraint

Adding a unique constraint (or a unique index) on the email column prevents duplicate inserts:

ALTER TABLE users ADD UNIQUE INDEX users_email_unique (email);

Now any attempt to insert a duplicate email raises a Duplicate entry error.

Problem Introduced by Soft Delete

Soft delete marks a row as removed by setting deleted_at (or is_active) instead of physically deleting it. The row stays in the table, so the unique constraint still sees the email as taken. When the same user tries to re‑register, the insert fails with a duplicate‑key error, even though the previous record is logically deleted.

PostgreSQL Solution: Partial Unique Index

PostgreSQL allows a unique index that only includes rows where deleted_at IS NULL:

CREATE UNIQUE INDEX users_email_unique ON users (email) WHERE deleted_at IS NULL;

This index ignores soft‑deleted rows, so only active users are subject to the uniqueness check. The syntax is straightforward and the intent is clear.

MySQL/MariaDB Solution: Generated Column + Unique Index

MySQL does not support partial indexes, but a generated (virtual) column can emulate the same behavior. First add a boolean column that is 1 for active rows and NULL for soft‑deleted rows:

ALTER TABLE users ADD COLUMN not_archived BOOLEAN GENERATED ALWAYS AS (
    IF(deleted_at IS NULL, 1, NULL)
) VIRTUAL;

Then create a composite unique index on (email, not_archived):

CREATE UNIQUE INDEX users_email_active_unique ON users (email, not_archived);

Because MySQL treats NULL values as non‑conflicting in a unique index, multiple soft‑deleted rows can share the same email, while only one active row can exist.

Multi‑Tenant Adaptation

When email uniqueness must be scoped to a tenant, include tenant_id in the index:

-- PostgreSQL
CREATE UNIQUE INDEX users_tenant_email_unique ON users (tenant_id, email)
WHERE deleted_at IS NULL;

-- MySQL
CREATE UNIQUE INDEX users_tenant_email_active_unique ON users (tenant_id, email, not_archived);

This allows the same email to appear in different tenants while preserving uniqueness within each tenant.

Key Takeaways

Ordinary unique constraints stop duplicates but break when soft delete is used.

PostgreSQL’s partial unique index ( WHERE deleted_at IS NULL) offers the cleanest solution.

MySQL/MariaDB achieve the same effect with a generated column that maps deleted rows to NULL and a composite unique index.

In multi‑tenant systems, add tenant_id to the unique index to keep uniqueness scoped per tenant.

Applying these patterns prevents the “multiple accounts with the same email” nightmare and makes debugging far easier.