Ad

Adding Constraint On Django Models Based On Values Of Another Field

- 1 answer

I have a simple model with 1 primary key and 3 fields (simplified):

  • passing score
  • max score
  • max attempt

This model is created by inheriting from django.db.models. This is the minimal reproducible code:

from django.db import models
class QuestionSet(models.Model):
    passingscore = models.PositiveSmallIntegerField("passing score")
    maxscore = models.PositiveSmallIntegerField("max score")
    maxattempt = models.PositiveSmallIntegerField("max attempt")

I would like to add a constraint such that passingscore should never be greater than maxscore in the database.

I've used constraint that span across multiple fields such as unique_together, as per this thread on StackOverflow. But clearly this is a different use-case.

I've also briefly considered to add constraint directly writing PostgreSQL code:

ALTER TABLE tableB ADD CONSTRAINT score_policy_1 CHECK (maxscore >= passingscore) 

But this defeats the purpose of using an ORM and violate the "loosely coupled" philosophy, making it hard to migrate between different database backends.

If this is at all possible, please point me to a more idiomatic way of writing this constraint in Django.

Ad

Answer

This should work in your case.

from django.db import models
class QuestionSet(models.Model):
    passingscore = models.PositiveSmallIntegerField("passing score")
    maxscore = models.PositiveSmallIntegerField("max score")
    maxattempt = models.PositiveSmallIntegerField("max attempt")
    
    class Meta:
        constraints = [
            models.CheckConstraint(
                name="%(app_label)s_%(class)s_passingscore_lte_maxscore",
                check=models.Q(passingscore__lte=models.F("maxscore")),
            )
        ]
Ad
source: stackoverflow.com
Ad