Using Django Check Constraints for the Sum of Percentage Fields2020-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:
- Pages we’ve read
- Pages we have left to read
- Pages we have deliberately chosen to ignore
We could use a Django model with one field for each of those three categories:
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
We always have to wrap the expression in a
Q()object. This represents a filter, and takes the same syntax as arguments to
F()objects to refer to the fields in our model.
We combine the
F()objects using Python’s mathematical operators. This doesn’t execute the operation but builds a representation that the database will execute after we migrate.
We have to write the expression with
percent_readon the left hand side. That is,
percent_read == 100 - percent_unread - percent_ignored, rather than the clearer
percent_read + percent_unread + percent_ignored == 100. This is due to limitations in Django’s
F()object that we might remove in a future version (e.g. PR #12041).
makemigrations gives us a migration like so:
This looks like our model definition.
The main difference is that the migrations framework has swapped the
F() objects for the constructed
sqlmigrate reveals the SQL it will execute:
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
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
QuerySet.exclude() to find the invalid objects:
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.
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:
try/except KeyError/elseto calculate the sum. The data dict can raise a
KeyErrorfor fields that the user didn’t provide. I covered using
elselike this in “Limit Your Try Clauses in Python”.
self.add_error(None, msg)to add a non-field error. This is better than
raise ValidationErrorsince it allows further steps we might add to
clean()to run as well.
A quick test of the form shows the clean method working:
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,
Working on a Django project? Check out my book Speed Up Your Django Tests which covers loads of best practices so you can write faster, more accurate tests.
One summary email a week, no spam, I pinky promise.
- Django's Field Choices Don't Constrain Your Data
- How to Disallow Auto-named Django Migrations
- Moving to Django 3.0's Field.choices Enumeration Types
© 2020 All rights reserved.