OOmnistu

Blog / Web & Ecommerce

Web & Ecommerce

Let a deleted account's email be reused, with a partial unique index

Let a deleted account email be reused with a SQLite partial unique index. Executed checks cover re-creation, conflicting restore, case folding and query plans.

OOmnistu
Reviewed 2026-11-18 · 4 min read

A team marks accounts as deleted instead of removing the row, so history and audit records stay intact. Then a user deletes their account and signs up again with the same email. If the table has a plain unique constraint on email, the new signup fails. The deleted row still holds the address.

SQLite documents a tool for this: the partial index. Its documentation says a partial index covers only the rows for which a WHERE clause is true, and that a unique partial index enforces uniqueness across just that subset. Its own example enforces one team leader per team with a unique index on team_id restricted to leaders. The same idea gives you one active account per email: a unique index limited to rows that are not deleted.

This guide tests that design in SQLite and checks the edges instead of only the happy path. It was prepared with AI assistance and reviewed on 18 November 2026. Other databases have their own partial or filtered index rules, which this article does not test.

The schema

CREATE TABLE accounts(
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL CHECK(length(email)>0),
  deleted_at TEXT);

CREATE UNIQUE INDEX uq_active_email
  ON accounts(lower(email)) WHERE deleted_at IS NULL;

The index is on lower(email) so ASCII case variants collide, subject to the Unicode limit below. Deleted rows, where deleted_at is set, are left out of the index and can repeat.

What the run checks

A contrast case comes first. With a full unique index on the same column, deleting an account and signing up again is refused. That is the problem. Then, with the partial index:

Each check asserts a result, and the script only passes when all of them hold. It ran on Python 3.10.12 with SQLite 3.37.2.

Two edge cases worth knowing

Restore is where the rule bites. Restoring is an update that makes a row active again, so it can collide with a newer active row. The application needs a clear message for that conflict, because the database error alone is not a user story. The test confirms the database refuses it, not what your product says.

Case folding has limits. SQLite's built-in lower() handles ASCII letters. The fixture inserts é@example.test and É@example.test, and both are accepted as different addresses. That is a limit shown, not fixed. If your users have non-ASCII addresses, normalize the value in the application before it reaches the index, or use a collation your database provides.

The query must repeat the condition

The SQLite documentation explains that a query can use a partial index only when its WHERE clause implies the index condition, and that SQLite uses simple rules to find the common cases. The fixture checks this with EXPLAIN QUERY PLAN. A lookup with lower(email)=? AND deleted_at IS NULL names uq_active_email. The same lookup without the deleted condition does not. So queries that look up active accounts should say so. This is a plan observation on one SQLite version, not a speed claim.

Limits

This tests in-memory databases in one process. It does not race two signups from separate connections, which is the case that matters most under load, and it does not exercise application code, other databases, or a migration of existing duplicate data. The run does confirm that creating the index over existing duplicate active rows fails, so clean those up first.

Sources