Constrain Django model by field value

Question

Consider the following Django model:

class Account(models.Model):

    ACCOUNT_CHOICES = [
        ('m', 'Main',),
        ('s','Secondary'),
        ('o', 'Other')
    ]
    user = models.ForeignKey(User)
    level = models.CharField(max_length=1, choices=ACCOUNT_CHOICES)

How can I enforce a database constraint of a maximum of one 'Main' account per user, while still allowing users any number of 'Secondary' or 'Other' accounts? In a sense, I want unique_together for user and level, but only when the value of level is m.

I know that I can manually check on saving, but I would prefer the database to check automatically and raise an IntegrityError when appropriate.

I don't think you can do that with your current model, but if those are the only choices for `level`, consider changing it to a nullable `BooleanField` such as `is_main = models.BooleanField(null=True)` and set it to `None` for secondary accounts. Then a `unique_together` will work because SQL will ignore null values. — Selcuk, Jun 19 '19 at 00:00
@Selcuk they're not the only options, so I'll update the question. However, your solution will work if I add a third field as you described — gatlanticus, Jun 19 '19 at 00:04
If you're using postgres you can do this with partial unique indexes http://sqlfiddle.com/#!17/c5441b/1 - here's a sqlfiddle for it - if this is what you're looking for, let me know and I'll create an answer out of it @ZG101 — Trent, Jun 19 '19 at 00:06
Yes, in this case you may add a third field and override `.save()` to have it automatically set to `None` if `level` is not `m`. @Trent's solution is better although less portable. — Selcuk, Jun 19 '19 at 00:09
@Trent Thanks for your suggestion. However, I'd rather stick to using the Django ORM and apply across other databases too — gatlanticus, Jun 19 '19 at 00:10
@Trent IMHO you should still post that as an answer to help future visitors. — Selcuk, Jun 19 '19 at 00:19
@Selcuk - are you able to add it as an appendix to your answer? Yours is accepted, it will likely see more traffic - my answer is neither Django based nor answers the request directly — Trent, Jun 19 '19 at 00:29
@Trent Actually it looks like it is finally possible to create partial indexes in Django ORM starting from Django 2.2. See [this question](https://stackoverflow.com/questions/26410488/creating-partial-indexes-with-django-1-7) for details. — Selcuk, Jun 19 '19 at 03:11

Selcuk · Accepted Answer · 2019-06-19T03:11:27.480

I don't think you can do that with your current model, but if those are the only two choices for the level field, consider changing it to a nullable BooleanField, for example

is_main = models.BooleanField(null=True)

and set it to None for secondary accounts. Then a unique_together will work because every null value is unique as far as SQL is concerned (see this answer).

Since there are more choices for the level field as you later clarified, you may add a third field and possibly override the .save() method to have it automatically set to None if level is not "m" for extra convenience.

Edit: If you are not concerned about portability, @Trent has suggested that PostgreSQL supports partial unique indexes, for example:

create unique index u_i on accounts(user_id, level_id) WHERE level_id = 'm';

Here is an SQL Fiddle.

Edit 2: Actually it looks like it is finally possible to create partial indexes in Django ORM starting from Django 2.2. See this question for details.

Great find on partial indexes in Django! – gatlanticus Jun 19 '19 at 03:21 — gatlanticus, Jun 19 '19 at 03:21

Constrain Django model by field value

1 Answers1