id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36198	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:

{{{#!python
# 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:

{{{#!sql
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.

{{{#!python
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  [https://docs.djangoproject.com/en/5.1/ref/models/constraints/#expressions 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 [https://forum.djangoproject.com/t/unique-constraint-on-datetime-date-unexpectedly-fails-validation-but-database-allows-saving/38889 django forum], see there for additional context and my efforts at debugging the validation step."	Bug	new	Database layer (models, ORM)	5.1	Normal		constraint lookup unique validation		Unreviewed	0	0	0	0	0	0