# CheckConstraint involving related model

**URL:** <https://forum.djangoproject.com/t/checkconstraint-involving-related-model/5351>\
**Category:** Using the ORM\
**Created:** [November 22, 2020, 2:22pm UTC](https://forum.djangoproject.com/t/checkconstraint-involving-related-model/5351 "2020-11-22T14:22:37Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![inglesp](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/inglesp/32/91_2.png) [@inglesp](https://forum.djangoproject.com/u/inglesp)\
**Post date:** [November 22, 2020, 2:22pm UTC](https://forum.djangoproject.com/t/checkconstraint-involving-related-model/5351/1 "2020-11-22T14:22:37Z")

</div>

I have two models:

```auto
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`:

```auto
    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:

```auto
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?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [November 22, 2020, 5:04pm UTC](https://forum.djangoproject.com/t/checkconstraint-involving-related-model/5351/2 "2020-11-22T17:04:02Z")

</div>

This is actually a PostgreSQL limitation. From their docs, the first Note in [section 5.4.1](https://www.postgresql.org/docs/12/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTShttps://www.postgresql.org/docs/12/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS):

> 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.)

---

<div class="post-metadata">

**Author:** ![adamchainz](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/adamchainz/32/26_2.png) [@adamchainz](https://forum.djangoproject.com/u/adamchainz)\
**Post date:** [November 22, 2020, 6:34pm UTC](https://forum.djangoproject.com/t/checkconstraint-involving-related-model/5351/3 "2020-11-22T18:34:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![inglesp](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/inglesp/32/91_2.png) [@inglesp](https://forum.djangoproject.com/u/inglesp)\
**Post date:** [November 22, 2020, 7:23pm UTC](https://forum.djangoproject.com/t/checkconstraint-involving-related-model/5351/4 "2020-11-22T19:23:23Z")

</div>

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.