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' => 'sam@example.com',
'password' => bcrypt(Str::random(8)),
]);
Users Table:
| id | name | email_verified_at | password | remember_token | created_at | updated_at | deleted_at | |
|---|---|---|---|---|---|---|---|---|
| 1 | Sam | sam@example.com | $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_verified_at | password | remember_token | created_at | updated_at | deleted_at | |
|---|---|---|---|---|---|---|---|---|
| 1 | Sam | sam@example.com | $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' => 'sam@example.com',
'password' => bcrypt(Str::random(8)),
]);
Illuminate/Database/QueryException with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'sam@example.com' for key 'users.users_email_unique' (SQL: insert into `users` (`name`, `email`, `password`, `updated_at`, `created_at`) values (Sam, sam@example.com, $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' => 'sam@example.com',
'password' => bcrypt(Str::random(8)),
]);
Users Table:
| id | name | email_verified_at | password | remember_token | created_at | updated_at | deleted_at | |
|---|---|---|---|---|---|---|---|---|
| 1 | Sam | sam@example.com | $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_verified_at | password | remember_token | created_at | updated_at | deleted_at | |
|---|---|---|---|---|---|---|---|---|
| 1 | Sam | 1596041868::sam@example.com | $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' => 'sam@example.com',
'password' => bcrypt(Str::random(8)),
]);
Users Table:
| id | name | email_verified_at | password | remember_token | created_at | updated_at | deleted_at | |
|---|---|---|---|---|---|---|---|---|
| 1 | Sam | 1596041868::sam@example.com | $2y$10$eNHbAmyL4DzTkclDeUbLBu3a9d3dmCpgEa7Ayd0utmJK/klKc3BXG | 2020-07-29 16:57:15 | 2020-07-29 16:57:48 | 2020-07-29 16:57:48 | ||
| 2 | Sam | sam@example.com | $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.
Top comments (2)
How to reduce query if when you mass import data, like thousands records?
i am wondering ... not tested but how about:
$table->string('email')->index();
$table->unique(['email', 'deleted_at']);