You add soft deletes to the users table, this looks harmless, you keep history (audit trail), you can restore a user, you keep all related rows alive.
Then someone tries to (re)register as user that was deleted, same email, and the database throws an error that the email is already taken. Reason: A (soft)deleted user still owns the unique email. So you get safety, but you lose easy account recreation. In this post, I'll walk through some practical ways on how to handle this.
Quick reminder, what are soft deletes?
When soft deletes are used, Laravel does not remove the row from the database, but fills the deleted_at property. All default queries ignore now the rows that have deleted_at filled in. The record still exists in the database. The email still exists. Any unique index on that column still applies. This is the root of the conflict.
Solutions
Do not soft delete users
This is the cleanest from the database point of view. If you delete a user, you really delete it. The database no longer has a row with that email. You can now create a new user with the same email without any tricks.
Advantages: You respect the unique index on email, the database does the work, the application does not need extra logic. You avoid edge cases where an old user is accidentally restored. You avoid sending mail to archived addresses. You can explain this to non technical people, deleted means gone.
Disadvantages: You lose the audit trail. You cannot prove that user X once existed. All relations that point to that user will break if you have foreign keys. If you need to restore someone because of support, you cannot. If privacy or accounting requires you to keep the identity, this does not help.
Use this if your application treats users as throwaway accounts, for example internal test users, or a small business admin panel, and you only care that two people cannot share the same email at the same time, then hard delete is fine. In many business apps this is not acceptable though, since users own records, tickets, payments or messages. Then hard delete creates more work.
Mutate the email on delete
This is the street smart fix. You keep soft deletes, but before you soft delete, you change the email so that the unique index is no longer blocked. When a user is deleted through the application, the model listens to the deleting event. In that event you rewrite the email. For example, you take the current email nico@super.be and you change it to deleted_nico@super.be. Then you let the delete run, so the record gets a deleted_at. The original email is now free. You can create a fresh user with nico@super.be right away.
Advantages: Works on every database. Very small change, you do not change the schema. You can keep every old user in the table. You free the email for a new account immediately.
Disadvantages: You are changing historic data. If you ever need to show the user history exactly as it was, the address will not be the real one (auditors may not like that). If you have an external system that reads your users table, it now sees fake emails. You must be careful that no one can log in with such a mutated mail. You must make sure no notification is ever sent to a mutated address. If someone is deleted many times, the email string becomes messy.
When to use it: This is a good fix if you are in production, you need a result today, and you cannot touch the database indexes. It is also good if the users table is already large and you do not want to rebuild indexes. Many teams end up doing this because it is easy to roll out.
Alter unique constraint so that it ignores deleted users
This option fits most production apps. You get audit. You get safety. You get the ability to recreate a user by email. You keep the rule close to the data (into the database). The main downside is that you have to add one database migration, and (potentially have to) alter your validation rules, which is acceptable for a users table.
So we want the database to say, email must be unique for active users only. Active means deleted_at is null. The easiest way to do this is to create a new migration:
public function up(): void
{
Schema::table('users', function (Blueprint $table) {
$table->dropUnique(['email']); // Drop the existing unique constraint on email
$table->unique(['email', 'deleted_at']); // Add a new unique constraint on email and deleted_at combination
});
}
All this does is removing the unique requirement from email and putting it on the combination of email and deleted_at. Which means, you can even delete a user multiple times without data consistency troubles.
Don't forget to adjust your validation rules from
- 'email' => ['string', 'max:255', 'email', Rule::unique('users')->ignore($user)],
+ 'email' => ['string', 'max:255', 'email', Rule::unique('users')->ignore($user)->whereNull('deleted_at')],
Advantages: The rule lives in the database, not in controller code. You can keep soft deletes, so you keep history. Admins can recreate users with the same email. Users can re-register. Your login code stays simple. You query active users only. You can still use withTrashed and onlyTrashed in Laravel.
Disadvantages: Make sure your user creation logic (validation) does not try to manually check for duplicates in PHP, the database will reject it anyway. For password resets, make sure you only query users where deleted_at is null, otherwise someone could get a reset mail for an archived user (but normally the SoftDeletes trait and standard auth logic takes care of this).
Some other options you may have
You can try to restore instead of recreate, which means that when an admin enters an email that already exists on a soft deleted user, the system restores that user and maybe resets the password, this keeps history very well but it can bring back old unwanted state so you must clean status, roles and tokens; you can separate identity from profile, for example put the email and unique login data in an identities table that is never soft deleted and put personal or employee data in a users table that can be soft deleted, this is cleaner in complex domains especially with SSO or external login, but it is a larger refactor and not needed for simpler apps; you can also move the user to an archive table on delete, for example copy all fields to archived_users and then hard delete from users, this keeps history out of the hot table so the unique index stays simple, but it makes restore harder since you must copy back - or you could use the spatie/laravel-deleted-models package; finally, you can give up on email uniqueness entirely and accept multiple rows with the same email, then use another immutable identifier for login, this solves the recreation issue but it breaks assumptions in many Laravel features that expect email to be unique.
Conclusion
For sure, if you are fine with losing history, delete users for real.
If you need the audit trail and want, like me, a long term and clean solution, use a constraint that only applies to non deleted users by running an extra migration. This keeps soft deletes on users useful without getting in your way.