Django 2.2: how to use to a field "from a foreign model", in a CheckConstraint: ?

[Olivier]

Hello,

Let say I want to enforce a database constraint saying a "book's author age is over 18".

As you may guess, books and authors are each described with a Model subclass with a foreign key (in this example each book is written by a single author) linking both.

class Author(models.Model):

    age = models.SmallIntegerField()

class Book(models.Model):

    author = models.ForeignKey(Author, on_delete=models.PROTECT)

    class Meta:
        constraints = [
           models.CheckConstraint(check=models.Q(<WHATEVER>__gt=18), name='age_check'),
        ]

I've tried replacing <WHATEVER> above with author_age or author__age.

In both cases, makemigrations works but migrate fails with:

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

Can I work around this ?

Any working example ?

Best regards

[Simon Charette]

Hello there,

I guess your example is not the best since this check could be defined on Author.Meta.constraints

directly but if you wanted to define such a check on Book anyway you'll have to define a function

and refer to it in the check constraint using Func.

Using RunSQL in your migrations

RunSQL("""

create or replace function author_age_gt(int, int) returns boolean as $$
select exists (
    select 1
    from "author"
    where id = $1 and age > $2
);
$$ language sql;

""")

And then you could define your CheckConstraint as

CheckConstraint(Func(F('author'), 18, function='author_age_gt'), name='age_check')

Cheers,

Simon

[Simon Charette]

For the record I haven' tested the above myself.

It might only work on Django 3.0+ and require you to pass `output_field=BooleanField()` to Func.

[Simon Charette]

So this happened not to be supported in Django 3.0 either.

But https://github.com/django/django/pull/12067 should add support for it.