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.