Using Unique Columns with Soft Deletes in Laravel
Tags: php, laravel, mysql
Published: 2020-07-29 17:25:42

---
title: Using Unique Columns with Soft Deletes in Laravel
published: true
description: Getting Laravel's soft-deletes to play nicely with your databases Unique constraint has never been easier.
tags: #php #laravel #mysql 
//cover_image: https://direct_url_to_image.jpg
---

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.

```php
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.

```php
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
```php
User::create([
    'name' => 'Sam',
    'email' => 'sam@example.com',
    'password' => bcrypt(Str::random(8)),
]);
```

Users Table:

| id | name | email           | 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
```php
User::find(1)->delete();
```

Users Table:

| id | name | email           | 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
```php
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](https://laravel.com/docs/7.x/eloquent#observers) to accomplish this with little effort.

####  1. Create a UserObserver class
```shell
php artisan make:observer UserObserver --model=User
```

####  2. Update the `delete` method to mutate the email
```php
public function deleted(User $user)
{
    $user->update([
        'email' => time() . '::' . $user->email
    ]);
}
```

####  3. Register UserObserver within our `AppServiceProvider`
```php
public function boot()
{
    User::observe(UserObserver::class);
}
```

### Demonstrating the Solution

####  1. Create User
```php
User::create([
    'name' => 'Sam',
    'email' => 'sam@example.com',
    'password' => bcrypt(Str::random(8)),
]);
```

Users Table:

| id | name | email           | 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
```php
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::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
```php
User::create([
    'name' => 'Sam',
    'email' => 'sam@example.com',
    '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::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](https://laravel.com/docs/7.x/eloquent-mutators#defining-an-accessor) for the email attribute to strip the prefixed data.