Constraint problem with SoftDeletableModel with unique field

[marcorichetta]

Hi everyone!

I'm having trouble with something I'm sure everyone using SoftDeletableModel went through.

Problem

Having a model with a unique field

class Product(SoftDeletableModel, TimeStampedModel, models.Model):
    test_field = models.CharField(unique=True,max_length=64)

Steps

1. Create an instance of that model. :bulb: Take note of the value of test_field.
2. SoftDelete that instance.
3. Try to create another instance with the same value on test_field as the one deleted.
4. DB constraint error.

Error

django.db.utils.IntegrityError: duplicate key value violates unique constraint 
"app_product_test_field_key" DETAIL:  Key (test_field)=(1234) already exists.

It seems to me that, before going to the DB, Django checks if the object already exists.

I believe the problem is that this check uses the model default manager, making impossible to see the soft deleted instance, and going to save it into the DB.

Ideas

I think some possible solutions would be:

• Telling the user that the register already exists but it needs to talk to some administrator
• Updating the soft-deleted register and changing to active again (This could lead to data overwriting :warning:)

Environment

• Django Model Utils version: 4.0.0
• Django version: 2.2.10
• Python version: 3.8.5
• Other libraries used, if any:

PD: I created a BaseModel for reutilization that inherits from SoftDeletable and TimeStamped models.

class BaseModel(SoftDeletableModel, TimeStampedModel, models.Model):

    class Meta:
        abstract = True

Code examples

class Product(SoftDeletableModel, TimeStampedModel, models.Model):
    test_field = models.CharField(unique=True,max_length=64)

instance = Product.objects.create(key='abc')
instance.delete()
instance2 = Product.objects.create(key='abc')

[sergei-iurchenko]

If you use postgres you can create partial index like

    class Meta:
        constraints = (
            UniqueConstraint(
                fields=('test_field',),
                condition=Q(is_removed=False),
                name='%(app_label)s_%(class)s_test_field_unique_idx',
            ),
        )

and delete unique=True from model field. Now all not deleted products have unique test_field but deleted can have non unique test_fields.

[auvipy]

If you use postgres you can create partial index like

    class Meta:
        constraints = (
            UniqueConstraint(
                fields=('test_field',),
                condition=Q(is_removed=False),
                name='%(app_label)s_%(class)s_test_field_unique_idx',
            ),
        )

and delete unique=True from model field. Now all not deleted products have unique test_field but deleted can have non unique test_fields.

this could be added to docs