Soft delete with unique constraint in Django

Question

I have models with this layout:

class SafeDeleteModel(models.Model):
    .....
    deleted = models.DateTimeField(editable=False, null=True)
    ......

class MyModel(SafeDeleteModel):
    safedelete_policy = SOFT_DELETE

    field1 = models.CharField(max_length=200)
    field2 = models.CharField(max_length=200)
    field3 = models.ForeignKey(MyModel3)
    field4 = models.ForeignKey(MyModel4)
    field5 = models.ForeignKey(MyModel5)

    class Meta:
        unique_together = [['field2', 'field3', 'field4', 'deleted'],]

The scenario here is that I never want users to delete data. Instead a delete will just hide records. However, I still want all non-soft-deleted records to respect unique key constraints. Basically, I want to have as many duplicated deleted records, but only a single unique un-deleted record can exist. So I was thinking to include "deleted" field (provided by django-safedelete library), but the issue becomes that Django's unique checks fail with "psycopg2.IntegrityError: duplicate key value violates unique constraint" for ['field2', 'field3', 'field4', 'deleted'] because NULL is not "equal to" NULL and it yields false in PostgreSQL.

Is there a way to enforce a unique_together constraint with the Django model layout as mine? Or is there a better idea to physically delete the record, then move it to an archive database, and if the user wants the record back, then software will look for the record in the archive and recreate it?

Answer 1

Yes, as of Django version 2.2 it is possible to use a UniqueConstraint with a condition.

Have a look at the documentation in this link: https://docs.djangoproject.com/en/2.2/ref/models/constraints/#uniqueconstraint

So your model would be something like this:

class MyModel(SafeDeleteModel):
    safedelete_policy = SOFT_DELETE

    field1 = models.CharField(max_length=200)
    field2 = models.CharField(max_length=200)
    field3 = models.ForeignKey(MyModel3)
    field4 = models.ForeignKey(MyModel4)
    field5 = models.ForeignKey(MyModel5)

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=['field2', 'field3', 'field4'],
                condition=Q(deleted=False),
                name='unique_if_not_deleted')
        ]

If you are using an older version of Django that doesn't have this feature available, you can create a migration with a partial unique index (have a look at this question here: Postgresql: Conditionally unique constraint).

As for your second question (would it be better to physically delete the record and move it elsewhere), it really depends on the characteristics of your application. If these soft-deletes don't happen very often and your table is still on the small side, I would keep the records in the same table for simplicity's sake, but if the number of records in the table starts growing fast and they affect the performance of the queries on this table then you should move the records elsewhere. You have to evaluate the trade-off between complexity and performance.