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.
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:
- A second active account with the same address is refused, and so is
A@Example.TEST. The table still holds one row. - Deleting an account sets
deleted_at. Deleting it again changes nothing. - The same address can be created again after the delete, and deleted again, and created a third time. The table ends with three rows, one of them active.
- Restoring the first deleted account is refused while the third is active. The failed restore leaves that row deleted, not half changed.
- After the active one is deleted, restoring the first works. Restoring a second old row then conflicts with it and is refused.
- Restoring an already active row is a no-op. An empty email and a NULL email are refused.
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
-
SQLite documentation, Partial Indexes: https://www.sqlite.org/partialindex.html
-
SQLite built-in scalar functions, lower(): https://www.sqlite.org/lang_corefunc.html