Adam Johnson

Home | Blog | Training | Projects | Colophon

Using Django Check Constraints for the Sum of Percentage Fields

2020-03-10

I previously covered using Django’s CheckConstraint class to ensure a field with choices is constrained to only valid values. Here’s another use case, based on an application I worked on. It uses a check constraint to ensure a set of fields, representing percentages, always sum up to 100.

Adding the Check Constraint

Imagine a book application where we want to track books our percentage progress in three categories:

We could use a Django model with one field for each of those three categories:

from django.db import models


class Book(models.Model):
    percent_read = models.PositiveIntegerField()
    percent_unread = models.PositiveIntegerField()
    percent_ignored = models.PositiveIntegerField()

    def __str__(self):
        return f"{self.id} - {self.percent_read}% read"

Using PositiveIntegerField means no field contains a number less than 0. That’s a good start, but the fields can still store numbers greater than 100, or their sum might be less or more than 100.

Using a check constraint, we can enforce such constraints, telling the database to prevent storage of bad data. In this case, we only need to enforce that their sum is 100 to automatically bound the individual fields between 0 and 100.

We add such a constraint in the model’s Meta.constraints:

from django.db import models


class Book(models.Model):
    percent_read = models.PositiveIntegerField()
    percent_unread = models.PositiveIntegerField()
    percent_ignored = models.PositiveIntegerField()

    def __str__(self):
        return f"{self.id} - {self.percent_read}% read"

    class Meta:
        constraints = [
            models.CheckConstraint(
                check=models.Q(
                    percent_read=(
                        100
                        - models.F("percent_unread")
                        - models.F("percent_ignored")
                    )
                ),
                name="percentages_sum_100",
            )
        ]

Notes:

Running makemigrations gives us a migration like so:

from django.db import migrations, models
import django.db.models.expressions


class Migration(migrations.Migration):

    dependencies = [
        ("core", "0001_initial"),
    ]

    operations = [
        migrations.AddConstraint(
            model_name="book",
            constraint=models.CheckConstraint(
                check=models.Q(
                    percent_read=django.db.models.expressions.CombinedExpression(
                        django.db.models.expressions.CombinedExpression(
                            django.db.models.expressions.Value(100),
                            "-",
                            django.db.models.expressions.F("percent_unread"),
                        ),
                        "-",
                        django.db.models.expressions.F("percent_ignored"),
                    )
                ),
                name="percentages_sum_100",
            ),
        ),
    ]

This looks like our model definition. The main difference is that the migrations framework has swapped the F() objects for the constructed CombinedExpression objects.

Running sqlmigrate reveals the SQL it will execute:

$ python manage.py sqlmigrate core 0002
--
-- Create constraint percentages_sum_100 on model book
--
ALTER TABLE `core_book` ADD CONSTRAINT `percentages_sum_100` CHECK (
    `percent_read` = (
        (100 - `core_book`.`percent_unread`)
        - `core_book`.`percent_ignored`
    )
);

We can apply this migration using migrate, but only if all the data in the table already fits the constraint. If it doesn’t, the database will raise an IntegrityError:

$ python manage.py migrate core 0002
Operations to perform:
  Target specific migration: 0002_percentages_sum_100, from core
Running migrations:
  Applying core.0002_percentages_sum_100...Traceback (most recent call last):
...
django.db.utils.IntegrityError: (4025, 'CONSTRAINT `percentages_sum_100` failed for `testapp`.`core_book`')

Unless you know for sure, it’s probably a good idea to check your production database is all valid before you try to deploy the migration. You can use the arguments passed to Q in QuerySet.exclude() to find the invalid objects:

Book.objects.exclude(percent_read=(
    100
    - models.F("percent_unread")
    - models.F("percent_ignored")
))

If you have such invalid data, you should fix it in a data migration that runs before the constraint migration.

After successfully adding the constraint, the database will raise that IntegrityError for inserts or updates of bad data.

Form Validation

As I wrote in my past post, constraints aren’t surfaced as nice error messages in forms. The best solution right now is to reimplement the constraint logic in Python in our form.

In this case we need to implement a Form.clean() method, since our validation is for more than one field:

from django import forms

from core.models import Book


class BookForm(forms.ModelForm):
    class Meta:
        model = Book
        fields = ["percent_read", "percent_unread", "percent_ignored"]

    def clean(self):
        cleaned_data = super().clean()

        try:
            percentages_sum = (
                cleaned_data["percent_read"]
                + cleaned_data["percent_unread"]
                + cleaned_data["percent_ignored"]
            )
        except KeyError:
            pass
        else:
            if percentages_sum != 100:
                self.add_error(
                    None,
                    (
                        f"Percentages must add up to 100%, they currently add"
                        + f" up to {percentages_sum}%"
                    )
                )

        return cleaned_data

Notes:

A quick test of the form shows the clean method working:

$ python manage.py shell
...
In [1]: from core.forms import BookForm

In [2]: BookForm({'percent_unread': 12, 'percent_read': 12, 'percent_ignored': 12}).errors
Out[2]: {'__all__': ['Percentages must add up to 100%, they currently add up to 36%']}

In [3]: BookForm({'percent_unread': 33, 'percent_read': 33, 'percent_ignored': 34}).errors
Out[3]: {}

Great!

Fin

During the writing of this blog post, I must admit I found a bug in Django, #31197. Adding this constraint won’t cleanly migrate on SQLite, on Django 2.2 or 3.0, but it’s fixed for 3.1 and I wrote a workaround on the ticket. Thanks to Simon Charette for writing the fix and Mariusz Felisiak for adding a test.

I hope this post helps you use check constraints more,

—Adam


Interested in Django or Python training? I'm taking bookings for workshops.


Subscribe via RSS, Twitter, or email:

One summary email a week, no spam, I pinky promise.

Related posts:

Tags: django