How can I have check constraint in django which check two fields of related models?

Question

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

class(models.Model):
    order_date = models.DateField()
    
class OrderLine(models.Model):
    order = models.ForeignKeyField(Order)
    loading_date = models.DateField()
    
    class Meta:

        constraints = [
        models.CheckConstraint(check=Q(loading_date__gte=F("order__order_date")), name="disallow_backdated_loading")

I want to make sure always orderline loading_date is higher than orderdate

Answer 1

A CHECK constraint can span over a column, or over a table, but not over multiple tables, so this is not possible through a CHECK constraint.

Some databases allow to define triggers. These triggers run for example when a records is created/updated, and can run SQL queries, and decide to reject the creation/update based on such queries, but currently, the Django ORM does not support that.

One could also work with a composite primary key of an id and the creation date of the order, in which case the create timestamp is thus stored in the OrderLine table, and thus one can implement a check at the table level, but Django does not support working with composite primary keys for a number of reasons.

Therefore, besides running raw SQL, for example with a migration file that has a RunSQL operation ^[Django-doc], but this will likely be specific towards a database.

Therefore probably the most sensical check is to override the model clean() method ^[Django-doc]. Django however does not run the .clean() method before saving an object in the database, this is only done by ModelForms, and ModelAdmins. We can thus add a check with:

from django.core.exceptions import ValidationError

class OrderLine(models.Model):
    order = models.ForeignKeyField(
        Order,
        on_delete=models.CASCADE
    )
    loading_date = models.DateField()
    
    def clean(self):
        if self.loading_date < self.order.order_date:
            raise ValidationError('Can not load before ordering')
        return super().clean()