CheckConstraint involving related model

Using Django Using the ORM
1

I have two models:

class Owner(models.Model):
    flag = models.BooleanField()

class Thing(models.Model):
    owner = models.ForeignKey("Owner", on_delete=models.CASCADE)
    n = models.IntegerField()

I want to constrain n so that it must be positive if the owner’s flag is True, and negative otherwise.

However, if I add the following constraint to Thing:

    class Meta:
        constraints = [
            models.CheckConstraint(
                name="some_constraint",
                check=Q(owner__flag=True, n__gt=0) | Q(owner__flag=False, n__lt=0),
            )
        ]

then running the created migration fails with:

django.core.exceptions.FieldError: Joined field references are not permitted in this query

Is this a limitation of Django, or a database limitation? Is there another way to achieve this?

2

This is actually a PostgreSQL limitation. From their docs, the first Note in section 5.4.1:

Note
PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked.

(This is, of course, assuming you’re using PostgreSQL. If you’re using a different database, you might want to check the corresponding docs.)

The only near-100% certain solution I’m aware of would be to write a custom trigger to perform this validation within the database. (You can’t do this in Django, because bulk updates don’t send signals or use the model’s save method, which would bypass the two most likely places where you could put this code.)

3

Hi Peter

This is a limitation in all databases, afaik. The only cross-table constraints allowed are foreign key constraints.

There’s probably a way involving a two field foreign key constraint and an extra table but it probably isn’t worth it.

If you really want such a constraint, you can instead use before-insert and before-update triggers to guard against the bad data cases. You’d need to create those with RunSQL operations only though, since Django’s ORM doesn’t support them.

HTH,

Adam

2 Likes
4

Hi Ken, hi Adam,

Thanks for your input. I’ve never had a good reason to learn about triggers… maybe today’s the day!

Peter.