Make soft deleted user email available again!

When creating user authorization system with soft-deletable data we might encounter a problem: deleted user might try to register with same email address and gets an error that it is in use. What to do in order to prevent it? Here is a quite simple example of how it could be solved.

First of all – by default Laravel migrations for users table have a unique index on email field. This needs to be modified – we need to have unique values on email and deleted_at fields at the same time. So let’s write our migration like this:

    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email');
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
            $table->softDeletes();

            $table->unique(['email', 'deleted_at']);
        });
    }

As you can see, we have a unique index for email and deleted_at at the same time. It is called a composite index. From now on – it is impossible to have two entries that would have identical information in both fields as long as none of them are NULL (except for a situation where your deleted_at field is set to NULL. This is not a bug due the fact that unique allows multiple NULL values in a column: http://dev.mysql.com/doc/refman/5.7/en/create-index.html – see comment below)

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

Now, to match a case where our user might not be deleted yet and we don’t want him to register with same email again – we need to change email validation rule:

Open our app/Http/Controllers/Auth/AuthController.php file (Request or other Controller where you have the validation rule) and change your email validation to this:

'email' => 'required|email|max:255|unique:users,email,NULL,id,deleted_at,NULL',

You might need to modify table name, column name and etc. for your needs.

And that’s it. Your user is able to register again with the same email as he did before and Laravel will make sure that the email is not within active users. Just don’t forget that when restoring it you need to check if there are no active users with identical email. This might not be the best solution for you, so we made a tiny list of other possible solutions. Feel free to choose any other if this doesn’t work for you or suggest us a new one!

  • Make a second table where you would store deleted users email and set a random string in the original database. On restore just copy the email back and delete the dummy row.
  • On user delete (using an observer or manually) prepend users email with a prefix: _deleted or something like that.
  • … your suggestions?
Like our articles?
Check out our Laravel online courses!

3 COMMENTS

  1. The downside to this approach is that you’ve lost the database constraint on ’email’. I.e. joe@example.com with a NULL deleted_at is NOT consider the same as another joe@example.com with a NULL deleted_at. As such, it’s possible to have both of these active Joe’s in the database at the same time. The only thing enforcing the desired constraint is the validation rule.

    A possible solution is to create an additional soft-delete column with a default value of 0 and a ‘deleted’ value of a timestamp. Use this in the database constraint instead of deleted_at.

    It’s also possible to customize Laravel to use a default soft-deleted value 0 instead of NULL. In practice though this has always caused many unexpected problems. Thus the duplicate soft delete indicator approach.

    Note: The above is true for MySQL, Postgres, etc. SQL Server 2000 is the exception to the above. Not sure about more recent versions of it though.

LEAVE A REPLY

Please enter your comment!
Please enter your name here