CODEWITHSUNDEEP
mysqldjango
ykaganovich
ykaganovich

Reputation: 14964

how to enforce a conditional uniqueness constraint

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?

Upvotes: 2

Views: 1019

Answers (3)

alper
alper

Reputation: 81

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')]

Upvotes: 1

cheeken
cheeken

Reputation: 34675

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')

Upvotes: 1

dannyroa
dannyroa

Reputation: 5571

Use model validation.

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

Upvotes: 0

Related Questions