Extending Django Constraints to ForeignKeys with DB triggers

February 24, 2023

Have you ever wanted to implement Django's check constraints and constrain a model field with checks on a particular field of a ForeignKey of the model? Django does not support it out of the box - because check constraints translate to database constraints, and no database supports it at the table constraint level. ¹ ²

Many Django developers use Model.save() or Django pre_save or post_save signal to do these checks. However, for large applications, these do not ensure data integrity in a way database constraints would have done. This is where database trigger is your tool of choice, offering a powerful mechanism to enforce data integrity rules at the database level for and keeping two models (database tables) constrained concerning certain real-world business rules

Scenario needing cross-model check constraint#

Consider a Django application for a property management firm that manages bookings and associated cash flows. Each Cashlow entry is tied to a Booking and has a field amount, which should not exceed the booking's payout. Ensuring the cashflow amount is within the booking payout limit is crucial for accurate financial management and reporting

from django.db import models

class Booking(models.Model):
    payout = models.PositiveIntegerField()

class Cashflow(models.Model):
    booking = models.ForeignKey(Booking, on_delete=models.CASCADE)
    amount = models.PositiveIntegerField()

Business dictates that the amount of a Cashflow instance should never exceed the payout of the associated Booking.

Django CheckConstraint does not support cross-model condition#

Suppose you were to hypothetically attempt to use a Django CheckConstraint to enforce that the amount in each Cashflow entry does not exceed the associated Booking's payout. In that case, you might write something like this in your Cashflow model:

from django.db import models
from django.db.models import Q, F, CheckConstraint

class Booking(models.Model):
    payout = models.PositiveIntegerField()

class Cashflow(models.Model):
    booking = models.ForeignKey(Booking, on_delete=models.CASCADE)
    amount = models.PositiveIntegerField()

+    class Meta:
+        constraints = [
+            CheckConstraint(
+                check=Q(amount__lte=F('booking__payout')),
+                name='amount_lte_payout'
+            ) 
+            # this will fail with django.core.exceptions.FieldError: 
+            # Joined field references are not permitted in this query
+
+        ]

Fails with:

$python manage.py migrate
..
..
django.core.exceptions.FieldError: Joined field references are not permitted in this query

The ORM way - model save() or pre/post save signals#

Django's ORM provides various ways to validate data, such as model clean methods, form validations, overriding the save method, or using pre_save Django signal.

class Cashflow(models.Model):
    booking = models.ForeignKey(Booking, on_delete=models.CASCADE)
    amount = models.PositiveIntegerField()

+   def save(self, *args, **kwargs):
+       # Perform custom validation before saving
+       if self.amount > self.booking.payout:
+           raise ValidationError("Cashflow amount cannot exceed Booking payout.")
+       
+       super(Cashflow, self).save(*args, **kwargs)

However, these methods have limitations:

They are not foolproof, as direct database manipulations can bypass them.
They can become cumbersome and less efficient as the application grows. You have to think about multiple places to do this "check" and always be on the lookout for code that bypasses the save method you wrote (like bulk_update, bulk_create, etc.). The downstream maintenance of your code is non-trivial with this approach

Using Database Triggers#

Implementing such checks in the database is the best approach.

Note: The below solution works specifically for PostgreSQL but most databases offer triggers and below can be adapted for them with small modifications

A PostgreSQL trigger is a function invoked automatically before or after a specified database operation (e.g., INSERT, UPDATE, DELETE) on a given table. Triggers can enforce complex business rules by validating data

For a detailed understanding of PostgreSQL triggers, refer to the official documentation.

While you can write the entire trigger yourself and put it in your migration file, I recommend using django-pgtrigger library

Integrating Triggers with Django using pgtrigger#

django-pgtrigger is a Django library that provides a Django-esque way of defining and managing PostgreSQL triggers. It allows you to define triggers within your Django models, keeping your data integrity rules close to your model definitions.

First, install it:

pip install django-pgtrigger

Implementing Our Trigger in PL/pgSQL#

We want a trigger that checks the amount against the payout of the related Booking before any insert or update operation on the Cashflow table. If the amount exceeds the payout, the operation should be prevented.

Here's how we can define such a trigger within our Cashflow model:

import pgtrigger

class Cashflow(models.Model):
    # Model fields...

    class Meta:
        triggers = [
            pgtrigger.Trigger(
                name='check_payout_limit',
                when=pgtrigger.Before,
                operation=pgtrigger.Insert | pgtrigger.Update,
                func=pgtrigger.Func(
                    f"""
                    DECLARE
                        related_payout integer;
                    BEGIN
                        SELECT payout INTO related_payout
                        FROM {Booking._meta.db_table}
                        WHERE id = NEW.booking_id;
                        
                        IF NEW.amount > related_payout THEN
                            RAISE EXCEPTION 'Cashflow amount cannot exceed Booking payout.';
                        END IF;
                        
                        RETURN NEW;
                    END;
                    """
                ),
            ),
        ]

In this code:

name='check_payout_limit' uniquely names the trigger, very similar to how you would name a CheckConstraint
when=pgtrigger.Before specifies the trigger fires before the specified operation (next point)
operation=pgtrigger.Insert | pgtrigger.Update tells fires the trigger BEFORE Insert and Ipdate operations in the Cashflow model's table
func=pgtrigger.Func(...) contains the PL/pgSQL function that implements our logic.
a related_payout variable is defined to store the value of Booking's payout amount for the Booking that is the ForeignKey to the Cashflow that is being currently dealt with (the NEW variable is the specific way in which PostgreSQL refers to the current db row that is being updated or created - and more specifically its 'new' value and not its 'old' value)
you can think of the trigger code logic sitting in a zone that is roughly equivalent to that of the pre-save of Django signals - it can refer to both the OLD (existing) and NEW (unsaved but about to be saved) object with these two special variables
spanning of the two database tables (which CheckConstraint could not do) happens here:

SELECT payout INTO related_payout
FROM {Booking._meta.db_table}
WHERE id = NEW.booking_id;

This translates to:

look up the Booking object's row using id=NEW for the current Cashflow row.Booking_id
then get it's payout and store in related_payout variable
finally do the check with if statement and throw error if business constraint not met

Note: In this case, we use the NEW variable, but OLD would have been fine if an existing Cashflow is updated and we need to do this check. But for new Cashflow create cases, NEW is the only option. using NEW in operation = pgtrigger.Insert | pgtrigger.Update is safest 2. {Booking._meta.db_table} inside the f-string will translate to yourapp_booking or whatever is the database name of your Booking model using Django's model meta. You could also hardcode the name of database table; I prefer to do it this way. 3. Django pgtrigger also gives you a mechanism to not hardcode the names of columns and tables by using its own interpolation; see here for details

Applying the Trigger#

With the heavy stuff done, you need to create and apply a migration for Django to set up the trigger in your PostgreSQL database:

python manage.py makemigrations
python manage.py migrate

This trigger encapsulates our data integrity rule within the Cashflow model, ensuring that our database automatically enforces this rule, maintaining the integrity of our data. Now you cannot create a Cashflow entry with an amount that exceeds the payout of the related Booking, or update an existing Cashflow entry to have an amount that exceeds the payout of the related Booking.

Further thoughts - implementing the reverse check trigger#

You might also want to implement the case where an existing Booking's payout is updated, and to keep things aligned; a check needs to be done where the Booking's cashflows don't have amounts that exceed the newly updated payout

The logic for this trigger is similar. It will sit in the Booking model's table and check the related Cashflow rows. I will cover this in a future article. I hope this helps ensure your data stays compliant with business logic.
Implementing similar constraint checks via triggers for Generic Foreign Keys (GFKs) is a step up in complexity in the same direction, where you need to do another lookup in the django_content_type table to find the correct table and then do your IF-THEN in PL/SQL

Notes

https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS

https://dev.mysql.com/doc/refman/8.3/en/create-table-check-constraints.html