PHPackages tranzakt/laravel-softdeletesunique - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. tranzakt/laravel-softdeletesunique ActiveLibrary[Database & ORM](/categories/database) tranzakt/laravel-softdeletesunique ================================== Package to allow correct unique indexing of null softDelete deleted\_at columns. v1.0-BETA2(3y ago)2111MITPHPPHP ^8.0|^8.1|^8.2 Since Dec 3Pushed 3y ago1 watchersCompare [ Source](https://github.com/Tranzakt/Laravel-SoftDeletesUnique)[ Packagist](https://packagist.org/packages/tranzakt/laravel-softdeletesunique)[ Docs](https://github.com/tranzakt/laravel-softdeletesunique)[ RSS](/packages/tranzakt-laravel-softdeletesunique/feed)WikiDiscussions main Synced 1mo ago READMEChangelog (2)Dependencies (5)Versions (3)Used By (0) Laravel-SoftDeletesUnique ========================= [](#laravel-softdeletesunique) The purpose of this extension is to make SQL unique constraints (Unique Indexes) work when you are using softDeletes on a table. Suppose you have a model with id and name fields - and you want name to be unique, so you create a Unique Index on the "name" field. If you create a record for 'Pete' and then (hard) delete it, then you can (of course) create a new record for 'Pete' without the name being duplicated. However, you then decide to use Soft Deletes, which adds a `deleted_at` field which is null for non-deleted records, and has a timestamp if the record is deleted. The idea, of course, is that models with Soft Delete work the same as it would without it, except that you can restore the deleted records. But if you create a record for 'Pete' and then soft delete it, then you want the model to behave the same as if you had hard deleted it and still allow you to create a new (non-deleted) record for 'Pete' alongside the deleted version. You therefore want the combination of `deleted_at` and `name` to be unique, so you create a unique index on `['deleted_at', 'name']` expecting it to prevent duplicates, i.e. in your migration, you replace... ``` $table->string('email')->unique(); ``` with: ``` $table->string('email'); $table->softDeletes(); $table->unique(['deleted_at', 'email']); ``` **However there is a gotcha here that is just waiting to getcha!**(and you probably won't explicitly test for this and it will be a problem waiting to happen). Unfortunately most (but not all) SQL RDBMS follow the SQL standard which defines every NULL value as being different from every other NULL value. Yes `NULL != NULL` (and that is NOT a typo!!), and that means that the unique index bizarrely allows you to have multiple entries `[NULL, 'Pete']`!!!, meaning that the Unique Index does **not** prevent duplicate records from being added. **This is the problem that this package solves.** It does it by creating a new column `deleted_at_uniqueable`which is maintained as a string version of the `deleted_at` column; using the empty string `''` if the `deleted_at` column is null. Your code now needs to look as follows: ``` $table->string('email'); $table->softDeletes(); $table->softDeletesUnique(); $table->unique(['deleted_at_uniqueable', 'email']); ``` Limitations ----------- [](#limitations) ### Eloquent-only [](#eloquent-only) In the same way that `softDeletes` is an Eloquent function and only works when you are using Eloquent and not when you are using QueryBuilder or raw DB, this package only maintains the `deleted_at_uniqueable` column when you are using Eloquent (though the SQL uniqueness will be enforced if you manually maintain this column in the same way that you would need to manually maintain the `deleted_at` column). ### Database maintained uniqueness vs. Validation [](#database-maintained-uniqueness-vs-validation) This package prevents insertion of a duplicate undeleted record at an SQL database level. If you attempt to insert a duplicate record, an `Illuminate\Database\QueryException` will be thrown (which you can (obviously) catch if you wish). For user requests to create or update a record, most developers will likely wish to ensure that the record will be unique at the request validation stage; this package can be used as an alternative or in addition to this sort of validation. When restoring trashed records, or for requests not directly initiated by the user, the developer will need either to implement manual checks that the result will be unique or use this package and catch any `QueryExceptions` that result. To validate the user request in the above example, you would typically have the following validation in your Requests: **Pre-softDeletes:** ``` public function rules() { return [ 'email'=>'required|unique:users' ]; } ``` **With softDeletes:** ``` public function rules() { return [ 'email'=>[ 'required', Rule:unique('users')->ignore($user) ->where(fn ($query) => $query->whereNull('deleted_at')) ] ]; } ``` **With softDeletes and softDeletesUnique:** ``` public function rules() { return [ 'email'=>[ 'required', Rule:unique('users')->ignore($user) ->where(fn ($query) => $query->where('deleted_at_uniqueable', '')) ] ]; } ``` ### Select performance [](#select-performance) When you select (non-deleted) records using a SoftDeletes model, i.e. you don't use the `withTrash()` or `onlyTrash()` modifiers, Laravel's Eloquent automatically adds `WHERE deleted_at IS NULL` to the SELECT query. A request validation testing for uniqueness will likely generate a select statement like this under the covers. For these types of queries, to enable the database optimiser to avoid a full table scan you will likely still need some sort of index on `deleted_at`. Since we are now using `deleted_at_uniqueable` for the unique index, you may need to create a non-unique index on the `deleted_at` field as well, i.e. your migration would need to look like... ``` $table->string('email'); $table->softDeletes(); $table->softDeletesUnique(); $table->index(['deleted_at', 'email']); $table->unique(['deleted_at_uniqueable', 'email']); ``` Installation & Usage ------------------------ [](#installation--usage) ### Installation [](#installation) ``` composer require Tranzakt/Laravel-SoftDeletesUnique ``` Once installed, softDeletesUnique support is automatically added to migration Blueprint objects. ### Usage [](#usage) **In your Migrations...** 1. Add `$table->softDeleteUnique()->after('deleted_at');` 2. Replace `$table->unique(['deleted_at', 'column']);` with `$table->unique(['deleted_at_uniqueable', 'column']);` 3. Add a non-unique index on `deleted_at` with `$table->softDeletes()->index();` or `$table->index('deleted_at');`. ``` public function up() { Schema::create('table_name', function (Blueprint $table) { ... $table->string('email'); $table->softDeletes()->index(); $table->softDeletesUnique(); $table->unique(['deleted_at_uniqueable', 'email']); }); } ``` **In your Models...** 1. Add `use Tranzakt\softDeletesUnique\Concerns\HasSoftDeletesUnique;` to the header and `use HasSoftDeletesUnique;` to the top of the class. ``` use Tranzakt\softDeletesUnique\Concerns\HasSoftDeletesUnique; class TableName extends Model { use HasSoftDeletesUnique; } ``` As normal, you can use a parameter on the `softDeletesUnique('deleted_at_str')` to create the column with a different name, and use `CONST DELETED_AT_UNIQUEABLE = 'deleted_at_str';` in your model to tell the model what the column name is. How it works ------------ [](#how-it-works) This package has been written to use the standard Laravel Eloquent facilities as fully as possible. The `softDeletesUnique` and `dropSoftDeletesUnique` methods are macroed into Blueprint. `$table->softDeletesUnique();` creates a new non-nullable string column `deleted_at_uniqueable` of up to 24 characters (format 'YYYY-MM-DD HH:MM:SS.xxxxxx'), that contains either '' when `deleted_at` is null, or a string representation if it is not null. The `HasSoftDeletesUnique` trait creates observers on the creating, updating, deleting and restoring Eloquent actions and ensures that the `deleted_at_uniqueable` column is set appropriately. And that's all folks. Alternatives ------------ [](#alternatives) This package is only one way to fix this database unique constraint issue, but it is believed to be the only common way of fixing it that works with all the Laravel supported RDBMS without change, and which doesn't require the coder to do any special DB:raw commands in the migrations. However, depending on the RDBMS you are using, there are alternative solutions (including where necessary an additional index to support the `WHERE deleted_at IS NULL`added by softDeletes): ### PostgreSQL / SQLite [](#postgresql--sqlite) Use 2 partial (filtered) indexes as follows: ``` CREATE UNIQUE INDEX active_email_unique ON MyTable (`email`) WHERE `deleted_at` IS NULL; CREATE UNIQUE INDEX deleted_email_unique ON MyTable (`deleted_at`, `email`) WHERE `deleted_at` IS NOT NULL; ``` Laravel Schema objects do **not** include the ability to define `WHERE` clauses on indexes, so you will need to use DB::raw to create and execute the above SQL data definition statements. Because we have separate partial indexes when `deleted_at` is both NULL and NOT NULL, the database should be able to use one of these indexes when Eloquent's softDelete functionality adds `WHERE deleted_at IS NULL` to the select statement. ### Microsoft SQL Server [](#microsoft-sql-server) Microsoft SQL Server considers NULL===NULL so that no special treatment is needed. ### MySQL / MariaDB [](#mysql--mariadb) Unfortunately neither MySQL nor MariaDB support indexes with `WHERE` clauses, and we need to use a "virtual column" instead. The raw SQL needed to create a virtual column and index it is as follows: ``` ALTER TABLE MyTable ADD COLUMN deleted_at_unique VARCHAR(19) GENERATED ALWAYS AS IF(`deleted_at` IS NULL, '-', `deleted_at`) VIRTUAL; CREATE UNIQUE INDEX email_unique_index ON MyTable(`deleted_at_unique`, `email`); ``` I haven't tested this, however I am doubtful whether this index would be used for the `WHERE deleted_at IS NULL` clause, so a non-unique index on `deleted_at` will likely also be needed for performance (with other columns if the index would be more useful with them added). The Laravel code for the above is: ``` $table->string('deleted_at_unique')->virtualAs('IF(`deleted_at` IS NULL, '-', `deleted_at`)'); $table->unique('deleted_at_unique', 'email'); ``` License ------- [](#license) This package is Licensed under the MIT open-source License. Acknowledgements ---------------- [](#acknowledgements) This package has been built by standing on the shoulders of others who have done the hard work of identifying both the issue and the solution. This package was originally authored by Sophist, with additional contributions from: . If you submit a PR, please add your name to the above list as part of your PR. ### Health Score 22 — LowBetter than 22% of packages Maintenance20 Infrequent updates — may be unmaintained Popularity9 Limited adoption so far Community8 Small or concentrated contributor base Maturity45 Maturing project, gaining track record Bus Factor1 Top contributor holds 100% of commits — single point of failure How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Every ~0 days Total 2 Last Release 1261d ago PHP version history (2 changes)v1.0-BETA1PHP ^7.4|^8.0 v1.0-BETA2PHP ^8.0|^8.1|^8.2 ### Community Maintainers ![](https://www.gravatar.com/avatar/99edd76a4583eed3d29fa63b2bbe3ad7e0783751bbeda4ad1baad55d632045e4?d=identicon)[Sophist-UK](/maintainers/Sophist-UK) --- Top Contributors [![Sophist-UK](https://avatars.githubusercontent.com/u/3001893?v=4)](https://github.com/Sophist-UK "Sophist-UK (50 commits)") --- Tags laraveltimestampsdeleted\_at ### Code Quality TestsPHPUnit ### Embed Badge ![Health badge](/badges/tranzakt-laravel-softdeletesunique/health.svg) ``` [![Health](https://phpackages.com/badges/tranzakt-laravel-softdeletesunique/health.svg)](https://phpackages.com/packages/tranzakt-laravel-softdeletesunique) ``` ### Alternatives [tpetry/laravel-postgresql-enhanced Support for many missing PostgreSQL specific features 9982.0M14](/packages/tpetry-laravel-postgresql-enhanced)[anourvalar/eloquent-serialize Laravel Query Builder (Eloquent) serialization 11120.2M21](/packages/anourvalar-eloquent-serialize)[biiiiiigmonster/hasin Laravel framework relation has in implement 154552.4k](/packages/biiiiiigmonster-hasin)[dragon-code/laravel-deploy-operations Performing any actions during the deployment process 240173.5k2](/packages/dragon-code-laravel-deploy-operations)[bavix/laravel-clickhouse Eloquent model for ClickHouse 72214.1k2](/packages/bavix-laravel-clickhouse)[sebastiaanluca/laravel-boolean-dates Automatically convert Eloquent model boolean attributes to dates (and back). 40111.7k1](/packages/sebastiaanluca-laravel-boolean-dates) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)