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

Adamec

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 – Marriage

Yes, you're right but the constraint can still be satisfied: The objects of both models must be created in a single database transaction. – Adamec

@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? – Doble

Enforcing the constraint immediately or only after transaction commit is both configurable: postgresql.org/docs/9.1/sql-set-constraints.html – Adamec

Here are approaches using SQL: #3881198 They need to define a function in SQL. It seems defining a constraint on other tables is not possible solely using python code. – Adamec

@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 – Doble

Answer 1

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.

Adamec

So is this possible at some way now? – Furl

@Furl 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. – Adamec