2

Say I have a bunch of tables where the objects are marked deleted rather than actually deleted. Now, I want to enforce a constraint that there can be only one non-deleted object with a particular set of field values, but I can have multiple deleted objects with the same field values.

class Deletable(models.Model):
    deleted = models.BooleanField(default=False)

    class Meta:
       abstract=True

    def soft_delete(self):
       self.deleted=True
       self.save()

class ConcreteModel(Deletable):
    a = models.IntegerField()
    b = models.IntegerField()

    class Meta:
       #wrong because there may have been some deleted rows
       unique_together=('a', 'b')

What is the best way to enforce the constraint?

ykaganovich
  • 14,736
  • 8
  • 59
  • 96

3 Answers3

1

Define your unique constraint across one more field: deleted and your pseudo-unique fields. Then, to represent a soft delete, assign the model's id to deleted; for undeleted items, assign 0.

With this approach, for undeleted items, since the deleted field is consistently-valued, the multi-field unique constraint will effectively ignore the value of the deleted and enforce uniqueness for just the pseudo-unique fields; for deleted items, deleted will be taken into account, and since it is unique, the constraint will always be satisified - so any number of models with the same pseudo-unique fields' values can coexist.

For example, the following code might be what you're looking for.

class Deletable(models.Model):
    deleted = models.IntegerField(default=0)

    class Meta:
       abstract=True

    def soft_delete(self):
       self.deleted=self.id
       self.save()

class ConcreteModel(Deletable):
    a = models.IntegerField()
    b = models.IntegerField()

    class Meta:
       unique_together=('a', 'b', 'deleted')
cheeken
  • 33,663
  • 4
  • 35
  • 42
  • Good idea. I'm going to do either this, or use null for deleted rows (which are treated as unique in all the databases I care about). I haven't decided which one yet :) – ykaganovich Jun 13 '12 at 20:59
  • Another approach is to use `NULL` instead of the id. Since SQL (including MySQL) considers NULLs as unique. – Paul Draper Jan 26 '23 at 17:30
1

For versions higher than Django 2.2, UniqueConstraint can be used.

On your model use:

from django.db.models import Q

class ConcreteModel(Deletable):
    a = models.IntegerField()
    b = models.IntegerField()

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['a', 'b'], condition=Q(deleted=False), name='a_b_unique')]
alper
  • 71
  • 1
  • 2
0

Use model validation.

https://docs.djangoproject.com/en/dev/ref/models/instances/#validating-objects

dannyroa
  • 5,501
  • 6
  • 41
  • 59