CODEWITHSUNDEEP
django
gatlanticus
gatlanticus

Reputation: 1226

Constrain Django model by field value

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.

Upvotes: 3

Views: 525

Answers (1)

Selcuk
Selcuk

Reputation: 59164

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.

Upvotes: 2

Related Questions