MySQL: Soft Delete + Unique Constraint [duplicate]

Question

I understand that soft deletion is useful for preserving data history and avoiding the need to manually handle child records. However, soft deletion can cause issues with unique constraints. For example, if the email column in the users table is unique, I can’t reuse email if it already exists in a soft-deleted row.

CREATE TABLE users (
  id INTEGER NOT NULL AUTO_INCREMENT,
  email VARCHAR(50) NOT NULL,
  deleted_at TIMESTAMP(0) NULL,

  UNIQUE INDEX `users_id_unique` (`id`),
  UNIQUE INDEX `users_email_unique` (`email`),
  PRIMARY KEY (`id`)
);

What are the best practices or efficient solutions for handling unique constraints alongside soft deletion in MySQL? Also, does Prisma provide any built-in support or recommended workaround for this scenario?

Yes, Conditional index would be solution in PostgreSQL. But MySQL sadly doesn't support it. — laitmiro
– laitmiro, Commented Jul 14 at 14:24
You would need to append something into the email field before deleting it. A timestamp would be best (in case the same user deletes account more than once.) or move the record to somewhere else, as in an audit table. — Jorge Campos
– Jorge Campos, Commented Jul 14 at 14:50
If you have mysql v8.013 or later, you can use index defined on expression to have a partial unique index: stackoverflow.com/questions/7804565/… In this case, your question is a duplicate. If you have an older version, then we may need to find another workaround. — Shadow
– Shadow, Commented Jul 14 at 14:51
FYI, you don't need an explicit UNIQUE INDEX on the primary key, it's automatically unique. — Barmar
– Barmar, Commented Jul 14 at 14:55

laitmiro · Accepted Answer · 2025-07-14 15:22:54Z

1

@Shadow's answer above worked. Posting the answer for others visit later.

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(50) NOT NULL,
  deleted_at VARCHAR(50) NULL,
  
  CONSTRAINT UNIQUE INDEX `users_email_unique` ((CASE WHEN deleted_at IS NULL THEN email END))
);

This allowed me to reuse the email of soft-deleted record(with the same email and not null value in deleted_at), while explicitly blocking duplicated email usage with null value at deleted_at

answered Jul 14 at 15:22

laitmiro

3241 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shadow Jul 14 at 15:47

In this case I vote to close the question as duplicate that places a link at the top for all visitors of SO to see for the answer that provided the solution.

Joshua Pinter Nov 13 at 14:00

You can simplify this quite a bit and just use IFNULL( deleted_at, 1 ) since deleted_at is a timestamp and should be unique by itself. If NULL then it will be set to 1, which will prevent duplicate values only for non-deleted User records.

Barmar · Accepted Answer · 2025-07-14 15:02:41Z

0

Use a multi-column unique index on (email, deleted_at). However, since null values are not equal to each other, this would allow duplicate rows with deleted_at = NULL. So rather than using NULL as the flag for the current row, you'll need some other sentinel value for this.

Another option is to add a generation number column. Whenever you reuse the email of a deleted row, you add 1 to its highest generation number. Then the unique constraint would be on (email, generation).

answered Jul 14 at 15:02

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

MySQL: Soft Delete + Unique Constraint [duplicate]

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related