Opened 5 years ago

Closed 5 years ago

#30484 closed New feature (fixed)

Add conditional expression support to CheckConstraint.

Reported by: David Sanders Owned by: Simon Charette
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (9)

comment:1 by David Sanders, 5 years ago

Summary: Feature request: Using RawSQL with CheckConstraintUsing RawSQL with CheckConstraint

comment:2 by Simon Charette, 5 years ago

Resolution: wontfix
Status: newclosed

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 the SQL at the Python level.

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')).

Last edited 5 years ago by Simon Charette (previous) (diff)

comment:3 by Gavin Wahl, 5 years ago

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

comment:4 by Simon Charette, 5 years ago

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.

Last edited 5 years ago by Simon Charette (previous) (diff)

comment:5 by Simon Charette, 5 years ago

Has patch: set
Resolution: wontfix
Status: closednew
Summary: Using RawSQL with CheckConstraintAdd conditional expression support to CheckConstraint.
Triage Stage: UnreviewedAccepted
Version: 2.2master

Re-opening since this isn't easily possible.

comment:6 by Simon Charette, 5 years ago

Owner: changed from nobody to Simon Charette
Status: newassigned
Last edited 5 years ago by Mariusz Felisiak (previous) (diff)

comment:7 by Mariusz Felisiak, 5 years ago

Patch needs improvement: set

comment:8 by Mariusz Felisiak, 5 years ago

Patch needs improvement: unset

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

Resolution: fixed
Status: assignedclosed

In e9a0e1d4:

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

Note: See TracTickets for help on using tickets.
Back to Top