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 , 5 years ago
Summary: | Feature request: Using RawSQL with CheckConstraint → Using RawSQL with CheckConstraint |
---|
comment:2 by , 5 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
comment:3 by , 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 , 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
.
comment:5 by , 5 years ago
Has patch: | set |
---|---|
Resolution: | wontfix |
Status: | closed → new |
Summary: | Using RawSQL with CheckConstraint → Add conditional expression support to CheckConstraint. |
Triage Stage: | Unreviewed → Accepted |
Version: | 2.2 → master |
Re-opening since this isn't easily possible.
comment:6 by , 5 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:7 by , 5 years ago
Patch needs improvement: | set |
---|
comment:8 by , 5 years ago
Patch needs improvement: | unset |
---|
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.
And use it for your constraint's condition
Q(total__isnulleq=F('total_last_updated'))
.