Use non-null default value for deleted_at to allow to better handle unique indexes

[dvisentin-freelance]

Hi,
a common way to handle unique indexes when using soft deletes in a way that allows to have multiple deleted records with the same vallue is to include the column used to indicate the deleted records (in our case, deleted_at) in the unique index. That column must have a different value for each deleted record (or at least for each deleted record that have the same value in the original unique index) and the same, non-null value for all the non-deleted records.

The current implementation conforms with the first condition, but not with the second, since the default value of the deleted_at column is null. So in mysql and many other dbms, if it's included in a unique index, the constraint is basically non applied, since every null is like a different value.

What I propose is to use Date::maxValue() as default value when creating the deleted_at column and when re-setting it with SoftDeletes::restore(). To maintain compatibility with existing databases, I think it may be sufficient to check both for null or Date::maxValue() in SoftDeletes::trashed().

[Sophist-UK]

I would personally propose that we use Date::minValue() as the default value` so that the live records appear first in the index.

I would further propose that (if the programmer has not manually incuded deleted_at in the unique index then) deleted_at is automatically included as the first key in unique indexes. The concept of softDeletes is that they supposed to act exactly the same as if the record had been hard-deleted unless the programmer explicitly asks for them to be included. If deleted_at is not included in the unique index, then having done a softDelete, you would be unable to insert a new non-softDeleted record with the same keys. Without this automatic inclusion, the programmer has to remember to include deleted_at and it may not occur to them to test for this situation and result in a bug at a later date.

[Sophist-UK]

P.S. See also rejected PR #42948

[Sophist-UK]

I have been doing some research and thinking on this - and (out of various possible approaches) I have come up with an idea that I will try out in the next couple of weeks as I have the time.

Use model events or other standard Laravel code to create a deleted_at_str column which is updated when deleted_at is updated and which contains a string value of the deleted_at field with '' when deleted_at is null. This field is never null and so unique constraints will be honoured.

The benefit of this is that this field is only ever updated, never read, and it will use standard Laravel functionality to do this, so it should be highly compatible with existing code and the run-time functionality could be achieved using a HasSoftDeleteUnique trait inside the users Model classes.

We would, however, need to have some way of creating the additional column, which would probably need a macroed Blueprint function to create a new field-type softDeleteUnique that the user would have to put in a migration, and they would also need to change their unique index definitions to use the new field. This would be included by using a softDeleteUnique trait inside the users Migration classes.

If anyone has any comments on whether this is likely to work, or might want to help with coding or testing, please let me know in further comments here.

@dvisentin-freelance Davide?

P.S. This would use the unique index to prevent creation of duplicate records, but the unique index would not be used for e.g. SELECTS with WHERE deleted_at IS NULL. I imagine that you may still require an index (unique or otherwise) whose first or only column is deleted_at in order to be able to identify non-deleted records using an index rather than a full table scan. Alternatively, perhaps it is possible to macro Eloquent softDelete functionality so that it uses WHERE deleted_at_str = "" instead (and you probably need deleted_at_str as the first unique index column for this index to be used).

[Sophist-UK]

https://github.com/Tranzakt/Laravel-SoftDeletesUnique
https://packagist.org/packages/tranzakt/laravel-softdeletesunique