CODEWITHSUNDEEP
pythondjangodatabasedjango-models
onlyphantom
onlyphantom

Reputation: 9603

Adding constraint on Django Models based on values of another field

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

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.

Upvotes: 8

Views: 4064

Answers (2)

Vladimir Prudnikov
Vladimir Prudnikov

Reputation: 7242

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

Upvotes: 11

heemayl
heemayl

Reputation: 42107

You can do it in either view layer or DB layer.

If you want to do it in the DB layer, you can override the save method of the model, and check values there:

def save(self, *args, **kwargs):

    if self.passingscore > self.maxscore:
        raise ValueError("passingscore can't be greater than maxscore")

    super().save(*args, **kwargs)

If you want to deal with the database constraints, you can create your own constraint class by subclassing BaseConstraint and put your sql in the constraint_sql method.

Upvotes: 3

Related Questions