Unique Constraints defined with __date lookups on F expressions fail validation but create database constraints sucessfully

[Tom Hall]

Unique constraints defined with date lookups on F expressions create the correct index and work as expected at the database/ORM level but raise false positives during validation.

Initially discovered this for date lookups but it would seem to apply to the other date/time lookups, at this point I have explicitly confirmed the same behaviour for year, iso_year and minute.

Minimal reproducable example:

# models.py
from django.db import models

class DateLookup(models.Model):
    timestamp = models.DateTimeField()

    class Meta:
        constraints = [
            # triggering validation errors for objects that can actually be saved
            models.UniqueConstraint(
                models.F("timestamp__date"),
                name="%(app_label)s_%(class)s_unique_date_F_expr",
            ),
        ]

# tests.py
import datetime as dt

from django.core.exceptions import ValidationError
from django.db import IntegrityError
from django.utils import timezone
from django.test import TestCase

from .models import DateLookup

TIMESTAMP = timezone.make_aware(dt.datetime(2025, 2, 18, 8, 18, 6))

class TestDateLookupConstraint(TestCase):
    def test_demonstrate_constraint_fails(self):
        """Demonstrate constraint validation failure at python level when db constraint is not triggered"""
        DateLookup.objects.create(timestamp=TIMESTAMP)
        new = DateLookup(timestamp=TIMESTAMP - dt.timedelta(days=1))
        new.save() #success
        self.assertTrue(new.pk)
        try:
            new.full_clean() #ValidationError
        except ValidationError:
            self.fail("False Positive")

    def test_constraint_db(self):
        """Demonstrate DB constraint is triggered sucessfully."""
        DateLookup.objects.create(timestamp=TIMESTAMP)
        new = DateLookup(timestamp=TIMESTAMP)
        with self.assertRaises(ValidationError):
            new.full_clean()
        with self.assertRaises(IntegrityError):
            new.save()
        self.assertFalse(new.pk)

Output of sqlmigrate command for SQLite backend:

BEGIN;
--
-- Create model DateLookup
--
CREATE TABLE "minimal_example_datelookup" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "timestamp" datetime NOT NULL);
CREATE UNIQUE INDEX "minimal_example_datelookup_unique_date_F_expr" ON "minimal_example_datelookup" ((django_datetime_cast_date("timestamp", 'UTC', 'UTC')));
COMMIT;

The workaround is to explicitly use the corresponding database functions in the constraint definition; this version works correctly.

class TruncDateExpression(models.Model):
    timestamp = models.DateTimeField()

    class Meta:
        constraints = [
            models.UniqueConstraint(
                models.functions.TruncDate("timestamp"),
                name="%(app_label)s_%(class)s_unique_date",
            ),
        ]

The ​docs for constraint expressions don't show any examples of lookups being used in this way but also don't explicitly disallow them either, and given that the correct constraint is in fact being generated on the database, it implies this could be fixed to work as expected. Otherwise, at a minimum the documentation should be updated to forbid the use of F expression lookups, and the constraint should fail to apply during migration.

Initially discovered this and investigated on the ​django forum, see there for additional context and my efforts at debugging the validation step.

[Sarah Boyce]

Thank you for the ticket!

I can replicate and I agree that if this is not allowed it is not clear in the docs
Here is a possible regression test

tests/constraints/models.py

@@ -73 +73 @@
     name = models.CharField(max_length=255)
     color = models.CharField(max_length=32, null=True)
     age = models.IntegerField(null=True)
+    updated = models.DateTimeField(null=True)
 
     class Meta:
         constraints = [

tests/constraints/tests.py

@@ +1 @@
+from datetime import datetime, timedelta
 from unittest import mock
 
 from django.core.exceptions import ValidationError
@@ -1030 +1031 @@
             exclude={"name"},
         )
 
+    def test_validate_expression_date(self):
+        updated_date = datetime(2005, 7, 26)
+        UniqueConstraintProduct.objects.create(name="p1", updated=updated_date)
+        constraint = models.UniqueConstraint(models.F("updated__date"), name="date_created_unique")
+        msg = "Constraint “date_created_unique” is violated."
+        with self.assertRaisesMessage(ValidationError, msg):
+            constraint.validate(
+                UniqueConstraintProduct,
+                UniqueConstraintProduct(updated=updated_date),
+            )
+        constraint.validate(
+            UniqueConstraintProduct,
+            UniqueConstraintProduct(updated=updated_date + timedelta(days=1)),
+        )
+
     def test_validate_ordered_expression(self):
         constraint = models.UniqueConstraint(

[Simon Charette]

To me this is a duplicate or at least closely related to #34871 and is due to the fact that models.F("timestamp__date") is not turned into something alike UnresolvedTransform(F("timestamp"), "date") which would allow proper replacement of F("timestamp") by Value(TIMESTAMP - dt.timedelta(days=1), DateLookup._meta.get_field("timestamp")).

The could be ways to resolve this particular issue by adapting F.replace_expressions when LOOKUP_SEP in self.name and the replacement (a Value instance in the case of constraint validation) has an output_field though which might be a less invasive solution.

[Simon Charette]

Submitted ​a PR demonstrating how this can be implemented for F.resolve_expression without going fully unboard with the Unresolved(Lookup|Transform) approach that might be necessary to fully resolve #34871.

[Tom Hall]

Replying to Simon Charette:

To me this is a duplicate or at least closely related to #34871 and is due to the fact that models.F("timestamp__date") is not turned into something alike UnresolvedTransform(F("timestamp"), "date") which would allow proper replacement of F("timestamp") by Value(TIMESTAMP - dt.timedelta(days=1), DateLookup._meta.get_field("timestamp")).

I did spot that one and meant to link to it in the description as maybe related but wasn't familiar enough at all with the underlying mechanics to be confident. And then I forgot. First time here and getting used to the system. Nice work on fixing it up, I suspected something in F.replace_expressions was needed.

Can try writing something up for the docs to demonstrate this being used if that would be helpful?

[Simon Charette]

Can try writing something up for the docs to demonstrate this being used if that would be helpful?

Thanks for the offer but we don't normally document bug fixing so in this case I don't think any documentation update is necessary.

I'll adjust the PR to include Sarah's test as well.