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!

score 8 · Answer 1 · edited Jun 20 '20 at 09:12

8

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 15 '19 at 10:56

Nour Wolf

2,140
25
24

score 7 · Answer 2 · answered Jan 27 '20 at 22:11

7

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

answered Jan 27 '20 at 22:11

Anthony

931
1
13
30

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. – Pran Kumar Sarkar Sep 08 '20 at 19:48

score 2 · Accepted Answer · edited May 23 '17 at 12:03

2

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.

edited May 23 '17 at 12:03

Community

1
1

answered May 10 '13 at 03:53

FMc

41,963
13
79
132

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. – Patrick Jun 06 '22 at 03:09
@Patrick Sounds plausible to me. My Django knowledge is outdated at this point. – FMc Jun 06 '22 at 05:52

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

3 Answers3