Add conditional expression support to CheckConstraint.

[David Sanders]

I'm wondering about the possibility of updating CheckConstraint to handle RawSQL expressions, in a similar way to annotate().

To illustrate the limitations of using Q objects and usefulness of RawSQL expressions consider the following example:

class Foo(models.Model):
    …
    total = models.IntegerField(blank=True, null=True)  # a calculated field
    total_last_updated = models.DateTimeField(blank=True, null=True)  # set when total is calculated

A check constraint would be useful here to enforce that total_last_updated is populated/cleared when total is populated/cleared and would be something like an XOR check with the following SQL expression: (total IS NULL) = (total_last_updated IS NULL). This doesn't appear to be possible with Q objects.

The workaround in this case is to create the check constraint manually with a RunSQL migration.

[Simon Charette]

It looks like this could be expressed as Q(total__isnull=RawSQL('(total_last_updated IS NULL)') but I haven't tested it.

When #25367 lands it should even work without RawSQL: Q(total__isnull=Q(total_last_updated__isnull=True)).

In the mean time if the above doesn't work I'd suggest you register ​a custom Lookup to express this filtering.

from django.db.models.fields import Field
from django.db.models import Lookup

class IsNullEq(Lookup):
    lookup_name = 'isnulleq'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return '(%s IS NULL) = (%s IS NULL)' % (lhs, rhs), params

Field.register_lookup(IsNullEq)

And use it for your constraint's condition Q(total__isnulleq=F('total_last_updated')).

[Gavin Wahl]

The other ticket is fixed, but it is still not possible to use expressions in check constraints. The proposed workaround with lookups doesn't work because my check constraint uses a function that takes multiple arguments (I need to be able to do CHECK num_nonnulls(a, b, c) = 1).

[Simon Charette]

This came out ​on the user mailing list today.

Wouldn't CheckConstraint(Func('a,' 'b', 'c', function='num_nonnulls')) work once support for expression is added in #30916?

Actually since BooleanField expression support was added to filter I _think_ it might actually already work on 3.0 by passing output_field=BooleanField() to Func.

[Simon Charette]

Re-opening since this isn't easily possible.

[Simon Charette]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In e9a0e1d4:

Fixed #30484 -- Added conditional expressions support to CheckConstraint.