Database Constraints in Django
Table of Contents
Data integrity refers to the accuracy of data stored inside a database. When creating web applications, data integrity becomes an important issue, and rules help enforce data integrity at various levels including inside Django by writing Python code or at the database level with constraints. I prefer using database constraints because they require very little code and maintenance, and the rule gets enforced regardless of where in the code the database table is used. A Django Model’s clean method is an alternative to database constraints. Using the clean method can be confusing because it is not called by a Model’s save method. I’ve found this leads to bugs and invalid data entering the database.
For this article, we’ll create a Django application to store employee data for various organizations. Part of our application allows organizations to set employee goals with a score between 1 and 5. The goal includes a baseline score where the employee starts and a target score where the employee should end.
import decimal from django.contrib.auth import get_user_model from django.contrib.postgres.constraints import ExclusionConstraint from django.contrib.postgres.fields import ( DateRangeField, RangeBoundary, RangeOperators, ) from django.core.validators import MaxValueValidator, MinValueValidator from django.db import models User = get_user_model() class DateRangeFunc(models.Func): function = "daterange" output_field = DateRangeField() class Organization(models.Model): name = models.CharField(max_length=255) class Employee(models.Model): class Meta: constraints = [ models.UniqueConstraint( fields=["user", "organization"], name="unique_employee_user" ) ] user = models.ForeignKey( User, on_delete=models.CASCADE, related_name="employees" ) organization = models.ForeignKey( Organization, on_delete=models.CASCADE, related_name="employees" ) class EmployeeGoal(models.Model): class Meta: constraints = [ ExclusionConstraint( name="exclude_overlapping_goals", expressions=( ( DateRangeFunc( "start_date", "end_date", RangeBoundary() ), RangeOperators.OVERLAPS, ), ("employee", RangeOperators.EQUAL), ), ), models.CheckConstraint( name="baseline_less_than_target", check=models.Q(baseline_score__lte=models.F("target_score")), ), ] employee = models.ForeignKey( Employee, on_delete=models.CASCADE, ) description = models.TextField() target_score = models.DecimalField( decimal_places=2, max_digits=3, validators=[ MinValueValidator(decimal.Decimal(1)), MaxValueValidator(decimal.Decimal(5)), ], ) baseline_score = models.DecimalField( decimal_places=2, max_digits=3, default=decimal.Decimal("2.50"), validators=[ MinValueValidator(decimal.Decimal(1)), MaxValueValidator(decimal.Decimal(5)), ], ) start_date = models.DateField() end_date = models.DateField()
Employee model contains a
unique constraint to ensure we don’t
duplicate an employee in an organization.
models.UniqueConstraint( fields=["user", "organization"], name="unique_employee_user" )
In the fields argument, we pass a list of model fields that should be unique together. We use the
Employee model to map
User model to an
Organization model that we defined above our
Employee model, and we want to ensure that
duplicate employee records don’t exist. Let’s say we have an organization, LAAC Technology; a User, Steven; and an employee
record linking these stored in our database. If we attempt to create another employee linking LAAC Technology and Steven, an
IntegrityError is raised, and the database prevents this new record from being created.
Our check constraint exists on the
EmployeeGoal model to prevent the goal’s baseline score from being greater than the target score. In our application,
an employee goal where the baseline score is greater than the target score doesn’t make sense because we want the
employee to improve not decline in score.
models.CheckConstraint( name="baseline_less_than_target", check=models.Q(baseline_score__lte=models.F("target_score")), ),
For the check argument, we pass a Q object which
contains our query, baseline score is less than or equal to the target score. We wrap the target score in an
F object, which lets us refer to the
value of target_score within the check constraint. If the baseline score is greater than the target score, the
check constraint results in an
IntegrityError and prevents the change in the database.
Django supports a PostgreSQL specific database constraint called the exclusion constraint. This constraint allows us to define complex rules for our database tables. To use this constraint, you need to run a migration to install the btree gist extension.
EmployeeGoal model, we only want one goal active at a time. To implement this, we use an exclusion constraint
to prevent overlapping date ranges for start date and end date per employee. We define a database function that
allows our constraint to determine the date range.
class DateRangeFunc(models.Func): function = "daterange" output_field = DateRangeField() ExclusionConstraint( name="exclude_overlapping_goals", expressions=( ( DateRangeFunc( "start_date", "end_date", RangeBoundary() ), RangeOperators.OVERLAPS, ), ("employee", RangeOperators.EQUAL), ), ),
The exclusion constraint allows us to pass in a list of expressions that make up the constraint. An expression consists
of a reference to a field or database function and a SQL operator. Our first expression says that the date range between
end_date should not overlap. Our second expression says that the employee should not be equal. Combining
these rules creates our exclusion constraint. Let’s say that our Steven employee of LAAC Technology has a goal with a
start date of Feburary 1, 2021 and end date of March 1, 2021. If we attempt to create an employee goal for Steven with a start
date of Februray 15, 2021 and an end date of March 15, 2021, the attempt results in an
IntegrityError, and the new
goal won’t be created.
Whenever I want to define rules for my application’s data, I use database constraints. In my experience, they result in the most consistent data because no matter where in the code a database table is operated on the rule is enforced. Occasionally, the database constraints are too limited, and I’ll be forced to write Python code. In these cases, I’ll reach for a Model’s clean method. Since this method is not called during a Model’s save method, you have to ensure that you call the clean method in the appropriate places such as inside a Form.