Using Unique Columns with Soft Deletes in Laravel

When you mix Laravel's soft deletion feature with your database's unique column constraints you're may have seen error messages that look something like SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ....

This can be a common scenario if you use soft-deletes on your users table, where you have a unique constraint on the email field. With soft deletes this means if a user deletes their account they'll be unable to recreate the account at a later date because the email still exists in the table. Unless we intend to write user-hostile software, we probably want the user to be able to re-register with the same email.

Let's dive into how we can mitigate this issue and allow users to be deleted without preventing the email from being used in the future.

Setup

To start off let's cover some basic assumptions about our application. We have a base Laravel user table migration, with the soft deletes attribute added.

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
    $table->softDeletes();
});

We also have the base Laravel User model, with the SoftDeletes attribute added.

class User extends Authenticatable
{
    use Notifiable, SoftDeletes;

    ...

Demonstrating the Problem

With this setup we can create uses and delete users, but creating a new user with the same email results in the "Integrity constraint violation" error.

1. Create User

User::create([
    'name' => 'Sam',
    'email' => '[e-mail]',
    'password' => bcrypt(Str::random(8)),
]);

Users Table:

id	name	email	email_verified_at	password	remember_token	created_at	updated_at	deleted_at
1	Sam	[e-mail]		$2y$10$OHcjoGZrF1zxSRLclThvbu5sNeiYdfzaxubzdJqZn64JtcAbauVai		2020-07-29 16:39:38	2020-07-29 16:39:38

2. Delete User

User::find(1)->delete();

Users Table:

id	name	email	email_verified_at	password	remember_token	created_at	updated_at	deleted_at
1	Sam	[e-mail]		$2y$10$OHcjoGZrF1zxSRLclThvbu5sNeiYdfzaxubzdJqZn64JtcAbauVai		2020-07-29 16:39:38	2020-07-29 16:40:56	2020-07-29 16:40:56

3. Recreating user produces an error

User::create([
    'name' => 'Sam',
    'email' => '[e-mail]',
    'password' => bcrypt(Str::random(8)),
]);

Illuminate/Database/QueryException with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '[e-mail]' for key 'users.users_email_unique' (SQL: insert into `users` (`name`, `email`, `password`, `updated_at`, `created_at`) values (Sam, [e-mail], $2y$10$DNrfq9wPEcSsOvFOX97tF.kiu2Zg.yRGqVvNPRRLi.BFtOK2fCbqC, 2020-07-29 16:41:30, 2020-07-29 16:41:30))'

Solution

The best way to get around this is to mutate the data contained in this unique column on delete. Now initially this might sound like a bad idea, since if you're using the soft deletion feature you probably want the data to remain in tact. But have no fear, we can mutate the data in a way that it remains readable.

With Laravel we can use Model Observers to accomplish this with little effort.

1. Create a UserObserver class

php artisan make:observer UserObserver --model=User

2. Update the delete method to mutate the email

public function deleted(User $user)
{
    $user->update([
        'email' => time() . '::' . $user->email
    ]);
}

3. Register UserObserver within our AppServiceProvider

public function boot()
{
    User::observe(UserObserver::class);
}

Demonstrating the Solution

1. Create User

User::create([
    'name' => 'Sam',
    'email' => '[e-mail]',
    'password' => bcrypt(Str::random(8)),
]);

Users Table:

id	name	email	email_verified_at	password	remember_token	created_at	updated_at	deleted_at
1	Sam	[e-mail]		$2y$10$eNHbAmyL4DzTkclDeUbLBu3a9d3dmCpgEa7Ayd0utmJK/klKc3BXG		2020-07-29 16:57:15	2020-07-29 16:57:15

2. Delete User

User::find(1)->delete();

In the database we can see the magic happening, now our email field contains a timestamp that makes this value unique.

Users Table:

id	name	email	email_verified_at	password	remember_token	created_at	updated_at	deleted_at
1	Sam	1596041868::[e-mail]		$2y$10$eNHbAmyL4DzTkclDeUbLBu3a9d3dmCpgEa7Ayd0utmJK/klKc3BXG		2020-07-29 16:57:15	2020-07-29 16:57:48	2020-07-29 16:57:48

3. Recreating user creates a new record

User::create([
    'name' => 'Sam',
    'email' => '[e-mail]',
    'password' => bcrypt(Str::random(8)),
]);

Users Table:

id	name	email	email_verified_at	password	remember_token	created_at	updated_at	deleted_at
1	Sam	1596041868::[e-mail]		$2y$10$eNHbAmyL4DzTkclDeUbLBu3a9d3dmCpgEa7Ayd0utmJK/klKc3BXG		2020-07-29 16:57:15	2020-07-29 16:57:48	2020-07-29 16:57:48
2	Sam	[e-mail]		$2y$10$NX62LTAQCbVn9uj3fjs4qeL55VnzCIflnGsG/xQ/8QlpXlbiONLIW		2020-07-29 16:59:03	2020-07-29 16:59:03

Conclusion

With the addition of a single Model Observer we're able to get around this issue and avoid a user-hostile experience. There are some draw backs to this, in particular the ability to restore soft-deleted records becomes tricky in the event that the user has created a new account. At that point however it's unlikely that you would need to restore the original record.

If you intend on restoring the record for reporting you can instead use the withTrashed attribute with a custom accessor for the email attribute to strip the prefixed data.

Comments

[vuongtran]

How to reduce query if when you mass import data, like thousands records?

[Active Dev]

i am wondering ... not tested but how about:
$table->string('email')->index();
$table->unique(['email', 'deleted_at']);