Is there any more elegant way to add a value sensitive unique together constraint in Django Model?

Question

Here is the problem:

I have a model like this:

class UserBook(models.Model):
    user = models.ForeignKey(User)
    book = models.ForeignKey(Book)
    is_active = models.BooleanField(default=False)

    class Meta:
        unique_together = ("user", "book")

Obviously, this model already has a unique together constraint for field user and book. And probably there will be some entries like this in the database:

    ------------------------------
    |user_id  book_id  is_active |
    |      1        1          0 |
    |      1        2          0 |
    |      1        3          1 |
    ------------------------------

And I have one more constraint to add, which is each user can have at most one entry that the value of is_active field is 1(True).

Currently I solve this problem by changing the model into this:

class UserBook(models.Model):
    user = models.ForeignKey(User)
    book = models.ForeignKey(Book)
    is_active = models.BooleanField(default=False)
    key = models.charFeild(max_length=255, unique=True)

    class Meta:
        unique_together = ("user", "book")

    def save(self, *args, **kwargs):
        if self.is_active:
            self.key = "%s_%s" %(self.user_id, self.is_active)
        else:
            self.key = "%s_%s_%s" %(self.user_id, self.is_active, self.book_id)

Add a field key, and customize the save method of this model.

But the max_length cannot be greater than 255 in this approach(which is no need to worry in my case, but sometimes the key field may be very long).

So, I would like to know if there is any more elegant approach to solve this kind of problem.

Thanks!

Frivol

Answer 1

Redefine the is_active to be as follows:

# Equals user ID if active; otherwise null.
is_active = models.IntegerField(null = True, unique = True)

The user IDs will be unique in the column (satisfying your desired constraint) and the many null values in the column won't violate the constraint, as discussed here.

Orelie

I initially used this but it meant that the is_active field can have 3 potential values: None, True, False and as such Django Admin would present the field as a drop down with 'Yes' / 'No' when editing the item as opposed to a tickbox (boolean). Using Anthony's method solved the problem and should be the accepted answer in 2022. – Parabolic

@Parabolic Sounds plausible to me. My Django knowledge is outdated at this point. – Orelie

Answer 2

In Django 2.2 (currently released as beta1) you will be able to use UniqueConstraint which in addition to the list of fields can be passed a condition

A Q object that specifies the condition you want the constraint to enforce.

For example, UniqueConstraint(fields=['user'], condition=Q(status='DRAFT') ensures that each user only has one draft.

Jasminjasmina

Answer 3

Based on Nour's answer, you can do this:

class Meta:
    constraints = [
        models.UniqueConstraint(
            fields=['user'],
            condition=Q(is_active=True),
            name='unique active user book per user'
        ),
    ]

Healthy

Unfortunately i am getting a wierd error could not create unique index 'unique user' as key(user, is_active)=(6,f) is duplicated. I am setting is_active = True but then also it is checking inactive users. – Kessia