djangodjango-modelsdjango-ormcheck-constraintsdjango-q

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


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


Solution

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