Django CheckConstraint: Elements of reverse ForeignKey lookup must not be empty

Question

I've got these models:

class Container(models.Model):
    ...
    class Meta:
        constraints = [
            models.CheckConstraint(
                 check=~Q(elements=None),
                 name='container_must_have_elements'
            ),
        ]

class Element(models.Model):
    container = models.ForeignKey(Container),
        related_name='elements',
        on_delete=models.CASCADE
    )

I want to enforce the constraint that every Container object must have at least one Element referencing it via the foreign key relation.

As you can see I already added a check constraint. However, the negation operator ~ on the Q object seems to be forbidden. I get django.db.utils.NotSupportedError: cannot use subquery in check constraint when I try to apply the generated migration.

Without the negation operator the constraint seems to be valid (it only fails due to a data integrity error).

Is there another way I can express this constraint so it is supported by CheckConstraint? (E.g. is there a way to check if the set of elements is not empty?)

Note that this constraint will generate a sort of circular dependency since you cannot create a `Container` without elements but in order to create an `Element` you need a container to reference. It seems to me that it makes no sense to have this constraint — ivissani, Feb 11 '20 at 17:12
Yes, you're right but the constraint can still be satisfied: The objects of both models must be created in a single database transaction. — Peter F, Feb 11 '20 at 17:46
@PeterF I'm not so sure about this single transaction. The Container still needs to be inserted into the db first in order to get a pk and only then can the Element be inserted. Won't the first insert raise an error then, i.e. are you sure constraints are checked only at the end of the transaction? — dirkgroten, Feb 11 '20 at 18:35
Enforcing the constraint immediately or only after transaction commit is both configurable: https://www.postgresql.org/docs/9.1/sql-set-constraints.html — Peter F, Feb 11 '20 at 18:41
Here are approaches using SQL: https://stackoverflow.com/questions/3880698/can-a-check-constraint-relate-to-another-table They need to define a function in SQL. It seems defining a constraint on other tables is not possible solely using python code. — Peter F, Feb 11 '20 at 18:51
@PeterF since the constraint isn't checked in python anyway, you could just not use `CheckConstraint` in your model but set it in your migration using [`RunSQL`](https://docs.djangoproject.com/en/3.0/ref/migration-operations/#runsql) — dirkgroten, Feb 11 '20 at 19:10

Peter F · Accepted Answer · 2022-05-12T13:59:11.163

8

I'll answer my own question by summarizing the question's comments.

A check constraint is intended to check every row in a table for a condition, which only takes the row itself into consideration and does not join other tables for this.

Sticking with SQL, one can formulate extended constraints including other tables by defining a function in SQL and calling it from within the constraint.

The CheckConstraint introduced in Django 2.2 only supports conditions on the table itself by using Q objects.

Update:

Since Django 3.1, CheckConstraints not only support Q objects but also boolean Expressions. See the Django 3.2 documentation.

edited May 12 '22 at 13:59

answered Feb 12 '20 at 09:21

Peter F

3,633
3
33
45

So is this possible at some way now? – wowkin2 May 12 '22 at 10:56
@wowkin2 Sorry, I haven't tried if this constraint can be expressed via a `CheckConstraint` using an `Expression`. I fixed the broken link though. I hope it helps. The constraint surely can be expressed in SQL. – Peter F May 12 '22 at 14:03

Django CheckConstraint: Elements of reverse ForeignKey lookup must not be empty

1 Answers1