How to use SoftDeletes and Unique together?

To achieve the desired functionality of allowing users to re-register with the same email after being soft deleted, you can indeed use a generated column. This column will help you enforce the uniqueness constraint only for active (non-deleted) users. Below is a detailed solution using Laravel migrations:

1. Create the Migration:

   You need to create a migration for the users table with the necessary columns, including the generated column active.

   use Illuminate\Database\Migrations\Migration;
   use Illuminate\Database\Schema\Blueprint;
   use Illuminate\Support\Facades\Schema;
   
   class CreateUsersTable extends Migration
   {
       /**
        * Run the migrations.
        *
        * @return void
        */
       public function up()
       {
           Schema::create('users', function (Blueprint $table) {
               $table->id();
               $table->string('email');
               $table->timestamps();
               $table->softDeletes();
               $table->boolean('active')->storedAs('IF(deleted_at IS NULL, 1, NULL)');
               $table->unique(['email', 'active']);
           });
       }
   
       /**
        * Reverse the migrations.
        *
        * @return void
        */
       public function down()
       {
           Schema::dropIfExists('users');
       }
   }
   

2. Explanation:

   • $table->id();: Creates an auto-incrementing primary key.
   • $table->string('email');: Creates an email column.
   • $table->timestamps();: Adds created_at and updated_at columns.
   • $table->softDeletes();: Adds a deleted_at column for soft deletes.
   • $table->boolean('active')->storedAs('IF(deleted_at IS NULL, 1, NULL)');: Adds a generated column active that is 1 if deleted_at is NULL (i.e., the user is not deleted) and NULL otherwise.
   • $table->unique(['email', 'active']);: Adds a unique constraint on the combination of email and active.

3. Usage:

   With this setup, when a user is soft deleted, the active column will be set to NULL, allowing another user to register with the same email. When querying for active users, you can simply check for active = 1.

4. Querying Active Users:

   When you want to query only active users, you can use the following Eloquent query:

   $activeUsers = User::where('active', 1)->get();
   

This solution ensures that the email uniqueness constraint is enforced only for active users, allowing re-registration with the same email after a user has been soft deleted.