How to soft delete rows without breaking unique constraints in PostgreSQL, MySQL and SQLite

To soft delete rows without breaking a unique constraint, make the uniqueness rule ignore deleted rows. In PostgreSQL and SQLite that is a partial unique index with WHERE deleted_at IS NULL. MySQL and MariaDB have no partial indexes, so you put the unique index on a generated column that is NULL for deleted rows, and rely on the fact that a unique index allows any number of NULLs.

Before you add either, check whether the deleted row should free its value. In the 32 soft-deleted tables in the drawDB gallery, 12 have a unique key, and only 3 of those keys should be released on delete. The other 9 are identifiers that have left the database, such as short links, public customer ids and subdomains, and reusing them would send someone else's traffic to the new owner.

Why does a soft delete break a unique constraint?

A soft delete is an UPDATE, not a DELETE. The row stays in the table with deleted_at set, so it stays in every unique index too. Delete the account for ana@example.com, try to sign up again with the same address, and the database rejects the insert because, as far as the index knows, that email is still taken.

The obvious fix, adding deleted_at to the unique key, quietly breaks the opposite way. Unique indexes treat NULLs as distinct in PostgreSQL, MySQL and SQLite, and every live row has deleted_at IS NULL, so two live rows with the same email no longer collide:

CREATE TABLE "accounts" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "email" VARCHAR(320) NOT NULL,
  "deleted_at" TIMESTAMPTZ,
  UNIQUE ("email", "deleted_at")
);

INSERT INTO "accounts" ("email")
VALUES ('ana@example.com'), ('ana@example.com');
-- INSERT 0 2: two live accounts with the same email

We ran that against PostgreSQL 16 and both rows went in. The constraint now looks right in the schema and enforces nothing for live rows, which are the only rows that matter.

Should a deleted row free its unique value?

Most soft deletes don't touch this question at all. Of the 32 tables in the gallery with deleted_at, archived_at or is_deleted, 20 have no unique key besides the id. They are messages, posts, comments and videos, soft-deleted because replies, reshares and notifications point at them, and the row survives as a tombstone that renders as "deleted".

The other 12 split cleanly by one question: has the value been used outside your database?

If the value has been printed, emailed, linked, synced or pasted into someone's code, a full unique index that still covers deleted rows is correct. The URL shortener does this on purpose: a deleted short code resolves to a 410 forever rather than being reissued.

Developer tools

URL shortener & link analytics database schema

The classic system-design question, modelled properly: short codes from a base62 counter, custom aliases and custom domains, an append-only click log with daily rollups, expiry and click limits, QR codes and blocked-domain safety checks.

Only values that live entirely inside your app, like a filename in a folder, should come back when the row is deleted. Those are the ones that need the techniques below.

How do you add a partial unique index in PostgreSQL?

Add a WHERE clause to the unique index, so deleted rows are not in the index at all:

CREATE TABLE "users" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "email" VARCHAR(320) NOT NULL,
  "deleted_at" TIMESTAMPTZ
);

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

With this index, soft-deleting Ana and signing her up again works, and a second live signup with the same email still fails with duplicate key value violates unique constraint "uq_users_email_live". It has to be an index: PostgreSQL's UNIQUE table constraint has no WHERE clause.

Two things change once the index is partial.

Upserts must repeat the predicate. ON CONFLICT ("email") alone does not match a partial index, and PostgreSQL answers with there is no unique or exclusion constraint matching the ON CONFLICT specification. The INSERT documentation calls this the index_predicate, and it must be written out so the planner can infer which index arbitrates the conflict:

INSERT INTO "users" ("email")
VALUES ('ana@example.com')
ON CONFLICT ("email") WHERE "deleted_at" IS NULL
DO NOTHING;

Queries must repeat it too. The planner uses a partial index only when the query's WHERE clause implies the index's predicate. WHERE "email" = $1 AND "deleted_at" IS NULL uses uq_users_email_live; WHERE "email" = $1 on its own does not. That is one more reason to put the live-rows filter in a view or a default ORM scope instead of in every query by hand.

One limit: a foreign key can't reference columns covered only by a partial unique index. The constraints chapter requires a primary key, a unique constraint or a non-partial unique index, and PostgreSQL rejects the foreign key with there is no unique constraint matching given keys. Point foreign keys at the surrogate id, which you should be doing anyway.

Is NULLS NOT DISTINCT a better fix?

PostgreSQL 15 added NULLS NOT DISTINCT, which makes a unique constraint treat NULLs as equal. On ("email", "deleted_at") it repairs the broken constraint from earlier: two live rows now collide because their NULLs match, while deleted rows differ by timestamp.

CREATE TABLE "accounts" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "email" VARCHAR(320) NOT NULL,
  "deleted_at" TIMESTAMPTZ,
  UNIQUE NULLS NOT DISTINCT ("email", "deleted_at")
);

It works, and it is a real constraint. We still prefer the partial index. It's smaller, because deleted rows are left out instead of indexed with a timestamp; the upsert target is just ("email"); and the pattern carries over to SQLite unchanged, while NULLS NOT DISTINCT is PostgreSQL-only. Pick NULLS NOT DISTINCT when you need a named constraint rather than an index, for example because a migration tool only understands constraints.

How do you do it in MySQL and MariaDB?

Neither has partial indexes. Instead, add a generated column that copies the email for live rows and is NULL for deleted ones, and put the unique index on that. The MySQL manual states that "a UNIQUE index permits multiple NULL values for columns that can contain NULL", so every deleted row can share the same NULL:

CREATE TABLE `users` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(320) NOT NULL,
  `deleted_at` DATETIME(6) NULL,
  `live_email` VARCHAR(320)
    AS (IF(`deleted_at` IS NULL, `email`, NULL)) VIRTUAL,
  UNIQUE KEY `uq_users_live_email` (`live_email`)
);

On MySQL 8.0.35, a soft-deleted email could be registered again and a duplicate live one failed with Duplicate entry 'ana@example.com' for key 'users.uq_users_live_email'. INSERT … ON DUPLICATE KEY UPDATE needs no changes, because MySQL checks every unique index.

MySQL 8.0.13 and later can skip the named column and index the expression directly, UNIQUE KEY ((IF(deleted_at IS NULL, email, NULL))), which we also confirmed rejects live duplicates. Prefer the named column anyway: lookups can say WHERE live_email = ? and hit the index without restating the expression exactly.

MariaDB has no functional indexes, but it supports indexes on virtual generated columns, so the same DDL applies. Query live_email by name there too: MariaDB versions before 11.8 cannot match an indexed generated-column expression written out in the WHERE clause.

SQL Server takes the PostgreSQL route with a filtered index, CREATE UNIQUE INDEX … WHERE deleted_at IS NULL.

How do you do it in SQLite?

The same way as PostgreSQL. SQLite has supported partial indexes, including unique ones, since 3.8.0:

CREATE TABLE "users" (
  "id" INTEGER PRIMARY KEY,
  "email" TEXT NOT NULL,
  "deleted_at" TEXT
);

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

INSERT INTO "users" ("email") VALUES ('ana@example.com')
  ON CONFLICT ("email") WHERE "deleted_at" IS NULL DO NOTHING;

SQLite enforces the predicate rule even more strictly than PostgreSQL. Leave the WHERE off the upsert and the statement fails to parse with ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint. And EXPLAIN QUERY PLAN shows the index for WHERE email = ? AND deleted_at IS NULL but a full SCAN users for WHERE email = ?. Offline-first apps hit this case constantly: the gallery's recipe app keeps every deleted row in its local SQLite store so the delete can sync to the server later.

What happens when you restore a soft-deleted row?

The partial index moves the conflict from delete time to restore time. Delete report.pdf, upload a new report.pdf to the same folder, then restore the old one from trash, and the restore fails:

UPDATE "files" SET "deleted_at" = NULL WHERE "id" = 42;
-- ERROR: duplicate key value violates unique constraint
--        "uq_files_folder_name_live" (SQLSTATE 23505)

That error is the index doing its job, so handle it in the restore path instead of dropping the constraint. Catch SQLSTATE 23505 (MySQL error 1062) and either restore under a new name, such as report (1).pdf, or ask the user which file wins. Don't check for a conflict first and restore second: two concurrent requests can both pass the check, and only the unique index sees both.

How do you migrate an existing unique index?

On a live PostgreSQL table, build the partial index first and drop the full one second, both without blocking writes:

CREATE UNIQUE INDEX CONCURRENTLY "uq_files_folder_name_live"
  ON "files" ("folder_id", "name")
  WHERE "deleted_at" IS NULL;

DROP INDEX CONCURRENTLY "uq_files_folder_name";

CONCURRENTLY can't run inside a transaction block, so these go in a migration with transactions disabled. The new index can't hit a duplicate on existing data: anything unique across all rows is also unique across live rows.

The Dropbox-style file storage schema in the gallery is a case for exactly this migration. Its DBML declares (folder_id, name) unique on files and (parent_id, name) on folders, and DBML has no syntax for a partial index, so the SQL that drawDB exports for every dialect is a full unique index. As exported, you can't upload a file whose name matches one in the trash. Export the schema from the editor, then apply the migration above, or the generated-column version for MySQL.

Productivity

Dropbox-style file storage & sync database schema

How a cloud drive models its data: a folder tree, versioned files, content-addressed blocks that deduplicate identical uploads to a single copy, shares and expiring public links, per-device sync cursors, trash retention and quota accounting.

Should you use an archive table instead?

An archive table moves deleted rows into a deleted_users table, so the live table keeps a plain unique index and every query is automatically "live only". It's the better design when the application never reads deleted rows again, for example a GDPR-driven purge where the copy exists only for audit.

It's the wrong design for the 20 tombstone tables in the gallery. Their rows are soft-deleted because other rows reference them: a reply needs its parent message to still exist, even as "This message was deleted". Moving the parent to another table breaks the foreign key or forces it to be dropped. If anything references the row, keep it in place and use a partial index. The schema design best practices post has the rest of the audit these numbers come from, including how often the gallery soft-deletes at all.

Start from a schema that already soft-deletes

Open the SaaS multi-tenant schema to see both cases in one place: organizations.slug stays reserved after deletion, and projects (organization_id, slug) should not. Fork it in the drawDB editor, export the DDL for your database, and swap in the partial index or generated column that matches each key.

Frequently asked questions