Keep Unique Constraints Working With Soft Deletes

Swap a plain unique constraint for a partial unique index so active rows stay unique while deleted rows stop reserving the value forever.

~30 minpostgresqlindexesdata-modeling

Task

Allow a person to register an email again after soft deletion without weakening uniqueness for active records.

When to use this

Accounts are soft deleted with a deleted_at timestamp, but the original UNIQUE(email) constraint still reserves the address forever, so support cannot recreate an account for the same person.

Prerequisites

PostgreSQL 12 or newer
A table with a nullable deleted_at column
A migration tool that can run raw SQL

Steps

1. Confirm the existing constraint and the index that enforces it.

psql -d appdb -c "SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'users'::regclass AND contype = 'u';"

Expected output: users_email_key | UNIQUE (email), which blocks reuse of a deleted address.

2. Check for existing duplicate active rows before creating the new index.

psql -d appdb -c "SELECT lower(email), count(*) FROM users WHERE deleted_at IS NULL GROUP BY 1 HAVING count(*) > 1;"

Expected output: Zero rows; any row returned must be merged or renamed before the index can be created.

3. Drop the constraint and create a partial unique index over active rows only.

ALTER TABLE users DROP CONSTRAINT users_email_key;
CREATE UNIQUE INDEX users_email_active_uidx
  ON users (lower(email))
  WHERE deleted_at IS NULL;

Expected output: The index exists with predicate (deleted_at IS NULL) and enforces case-insensitive uniqueness for active rows.

4. Prove both behaviours with a deleted row and two active inserts.

INSERT INTO users (email, deleted_at) VALUES ('[email protected]', now());
INSERT INTO users (email) VALUES ('[email protected]');
INSERT INTO users (email) VALUES ('[email protected]');

Expected output: The first active insert succeeds; the second fails with SQLSTATE 23505 on users_email_active_uidx.

5. Keep ON CONFLICT working by repeating the index predicate in the statement.

INSERT INTO users (email) VALUES ('[email protected]')
ON CONFLICT (lower(email)) WHERE deleted_at IS NULL DO NOTHING;

Expected output: The upsert infers the partial index and inserts once without error.

Troubleshooting

Problem

ON CONFLICT (email) fails with there is no unique or exclusion constraint matching

Solution

Partial indexes require the predicate in the conflict target: ON CONFLICT (lower(email)) WHERE deleted_at IS NULL.

Problem

Restoring a deleted row conflicts with an active row

Solution

Handle restore explicitly: if an active row already uses the value, merge into it or return a conflict instead of clearing deleted_at blindly.

Limitations

Queries must include deleted_at IS NULL to use the partial index; without the predicate PostgreSQL falls back to a sequential scan.
The index permits many deleted rows with the same email, which is normally desired but must be understood by reporting queries.
Case-insensitive matching only works if every writer uses lower(email); enforce it in the application layer.

The soft-delete contradiction

Soft deletion keeps the row so history and foreign keys survive, but a plain UNIQUE constraint cannot tell a live row from a tombstone. The address stays reserved forever, which is usually not the business rule anyone wanted. Teams then reuse the deleted row, overwriting history, or drop uniqueness entirely, which allows two active accounts with the same email.

A partial unique index expresses the real rule

The rule is “unique among active rows”. PostgreSQL partial indexes encode exactly that: the index contains only rows matching WHERE deleted_at IS NULL, and uniqueness applies only inside the index. Adding lower(email) also makes the rule case-insensitive.

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

Because the index is also smaller, lookups that filter on the same predicate stay fast even as the archive grows.

Align every write path

The index only guards inserts and updates that could produce a conflicting active row. ON CONFLICT must repeat the predicate so the planner can infer the index, and any upsert written before the change will start failing. Search the codebase for ON CONFLICT and for raw INSERT ... users statements; ORMs rarely model partial indexes and may generate a full unique constraint in the next migration.

Align reads too

A query without deleted_at IS NULL cannot use the partial index and may silently fall back to a sequential scan. Most application reads already exclude deleted rows, but admin and reporting queries often do not. Add the predicate or a separate full index, and check plans with EXPLAIN (ANALYZE, BUFFERS).

Clean up and test

Before creating the index, find active duplicates:

SELECT lower(email), count(*) FROM users
WHERE deleted_at IS NULL GROUP BY 1 HAVING count(*) > 1;

Merge or rename them first, then run the migration. Test the three cases explicitly: a deleted row plus a new active insert succeeds, a duplicate active insert fails with 23505, and restoring a deleted row whose address is now active returns a controlled conflict instead of a constraint error.