Unique Constraint on datetime__date unexpectedly fails validation but database allows saving

Run into issues recently with being unable to save models in the admin due to a unique constraint failing validation when it shouldn’t.
This first showed up when I was unable to edit existing objects in the admin as they failed the validation there. However I was able to get them into the db via the ORM directly. So the constraint is raising a false positive on the python side.

Similar posts on here didn’t show up this behaviour. However the documentation on constraints is quite thin so posting on here incase I’m missing something before filing a ticket for it.

Minimal example with test to reproduce:

# models.py
from django.db import models

class Container(models.Model):
    name = models.CharField(max_length=10)

class Timestamped(models.Model):
    timestamp = models.DateTimeField()
    key = models.IntegerField()
    container = models.ForeignKey(Container, on_delete=models.CASCADE)
    test_label = models.TextField(max_length=50, blank=True)

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

# tests.py
import datetime as dt

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

from uniqueconstraintbug.models import Container, Timestamped

class TestUniqueConstraintOnDateTime(TestCase):

    @classmethod
    def setUpTestData(cls):
        cls.ts = dt.datetime(2025,2,13,10,2,0, tzinfo=dt.UTC)
        cls.ts_same_date = dt.datetime.combine(cls.ts, dt.time(21,30), tzinfo=dt.UTC)
        cls.ts_different_date = cls.ts - dt.timedelta(days=7)

        cls.a = Container.objects.create(name='A')
        cls.b = Container.objects.create(name='B')

        cls.existing = Timestamped.objects.create(timestamp=cls.ts, container=cls.a, key=1)

    # other tests verify the constraint is working as expected in other situations but left out here to save space

    def test_unexpected_constraint_failures_on_timestamp(self):

        new_date = Timestamped(timestamp=self.ts_different_date, container=self.a, key=1, test_label="new_date")

        # test will fail here unless we assert the validation error
        # even though date is different
        with self.assertRaises(ValidationError):
            new_date.validate_constraints()

        # can save even though validation error raised
        new_date.save()
        self.assertTrue(new_date.pk)
        self.assertNotEqual(new_date.timestamp.date(), self.existing.timestamp.date())

The constraint does work as expected if all three fields are the same, or for different values of key and container between instances, so it seems like somewhere along the process something about the timestamp comparisons is going wrong.

Edit
Extra data I forgot to append. Stack trace from failing version of test:

python manage.py test uniqueconstraintbug.tests.TestUniqueConstraintOnDateTime.test_unexpected_constraint_failures_on_timestamp
Found 1 test(s).
Creating test database for alias 'default'...
System check identified no issues (0 silenced).
E
======================================================================
ERROR: test_unexpected_constraint_failures_on_timestamp (uniqueconstraintbug.tests.TestUniqueConstraintOnDateTime.test_unexpected_constraint_failures_on_timestamp)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/tom/dev/projects/training_log/uniqueconstraintbug/tests.py", line 51, in test_unexpected_constraint_failures_on_timestamp
    new_date.validate_constraints()
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^
  File "/Users/tom/dev/projects/training_log/.venv/lib/python3.13/site-packages/django/db/models/base.py", line 1604, in validate_constraints
    raise ValidationError(errors)
django.core.exceptions.ValidationError: {'__all__': ['Constraint “uniqueconstraintbug_timestamped_unique_key_per_date_per_container” is violated.']}

----------------------------------------------------------------------
Ran 1 test in 0.002s

FAILED (errors=1)
Destroying test database for alias 'default'...

Output from sqlmigrate showing what seems to be correct creation of constraint at DB level in SQLite3:

-- Create model Timestamped
--
CREATE TABLE "uniqueconstraintbug_timestamped" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "timestamp" datetime NOT NULL, "key" integer NOT NULL, "container_id" bigint NOT NULL REFERENCES "uniqueconstraintbug_container" ("id") DEFERRABLE INITIALLY DEFERRED, "test_label" text NOT NULL);
--
CREATE UNIQUE INDEX "uniqueconstraintbug_timestamped_unique_key_per_date_per_container" ON "uniqueconstraintbug_timestamped" ((django_datetime_cast_date("timestamp", 'UTC', 'UTC')), "container_id", "key");
---

Django 5.1.5, Python 3.13.1

1 Like

Please identify which database engine you are using, and post the result of a manage.py sqlmigrate command on the migration creating this constraint.

SQLite for this one, and I pulled out what seemed to be the relevent rows from an sqlmigrate information above into the original post. I did suspect something to do with SQLites date handling initially. But again, at DB level it appears to work perfectly.

I tried putting a debugger into UniqueConstraint.validate and stepping through there. Though I’m very new to using debuggers and stepping through there got really hard to follow with the iteration across model fields and queryset construction, it did seem like it was failing to filter the existing database entries on timestamp properly.

Somewhere around here seems to be where it was going wrong:

    def validate(self, model, instance, exclude=None, using=DEFAULT_DB_ALIAS):
           #/django/db/models/constraints.py:640
            replacements = {
                F(field): value
                for field, value in instance._get_field_expression_map(
                    meta=model._meta, exclude=exclude
                ).items()
            }
            filters = []
            for expr in self.expressions:
                if hasattr(expr, "get_expression_for_validation"):
                    expr = expr.get_expression_for_validation()
                rhs = expr.replace_expressions(replacements)
                condition = Exact(expr, rhs)
                if self.nulls_distinct is False:
                    condition = Q(condition) | Q(IsNull(expr, True), IsNull(rhs, True))
                filters.append(condition)
            queryset = queryset.filter(*filters)

Can confirm that the constraint works as expected if defined by an explicit TruncDate database function as in the following diff:

             models.UniqueConstraint(
-                models.F("timestamp__date"), "container", "key",
+                TruncDate("timestamp"), "container", "key",
                 name="%(app_label)s_%(class)s_unique_key_per_date_per_container",
             ),

SQL generated for the migration remains exactly the same:

CREATE UNIQUE INDEX "uniqueconstraintbug_timestamped_unique_key_per_date_per_container" ON "uniqueconstraintbug_timestamped" ((django_datetime_cast_date("timestamp", 'UTC', 'UTC')), "container_id", "key");

So thats certainly a usable workaround, but I guess I’m left wondering why the implict field lookup didn’t work? Is it just not supported in this context? The docs on using expressions in constraints are not very clear, but the only examples shown are for explicit database functions or expressions composed of F objects that do not use lookups.

That is a really interesting find.

I’d be curious to see if the same symptoms appear with a different database engine.

I know you wrote:

but, as you point out, it is building a query to perform the validation - which leads to the possibility that the query being built isn’t quite “right” - and there is the possibility that it is database-engine related.

I’d probably start by looking at this from the other direction. I think I’d want to capture the actual query being issued here. What is the “in Django” function doing that doesn’t properly represent what’s supposed to happen in the database?

Had another go with the debugger and got some more out of it this time, by running both the “good” and “bad” versions of the constraint through the test example I’ve got here.

Happy path

(Pdb) b django.db.models.constraints:647
(Pdb) c
(Pdb) p self.expressions
(TruncDate(F(timestamp)), F(container), F(key))
(Pdb) pp replacements
{F(id): Value(None),
 F(timestamp): Value(datetime.datetime(2025, 2, 6, 9, 42, tzinfo=datetime.timezone.utc)),
 F(key): Value(1),
 F(container): Value(1),
 F(test_label): Value('new_date'),
 F(pk): Value(None)}

# step through a few lines to first pass through loop to create filters
(Pdb) p expr
TruncDate(F(timestamp))
(Pdb) p rhs
TruncDate(Value(datetime.datetime(2025, 2, 6, 9, 42, tzinfo=datetime.timezone.utc)))
(Pdb) p condition
Exact(TruncDate(F(timestamp)), TruncDate(Value(datetime.datetime(2025, 2, 6, 9, 42, tzinfo=datetime.timezone.utc))))


Sad Path

(Pdb) b django.db.models.constraints:647
(Pdb) c
(Pdb) p self.expressions
(F(timestamp__date), F(container), F(key))
(Pdb) pp replacements
{F(id): Value(None),
 F(timestamp): Value(datetime.datetime(2025, 2, 6, 9, 42, tzinfo=datetime.timezone.utc)),
 F(key): Value(1),
 F(container): Value(1),
 F(test_label): Value('new_date'),
 F(pk): Value(None)}

# step through a few lines to first pass through loop to create filters
(Pdb) p expr
F(timestamp__date)
# this looks like the culprit
(Pdb) p rhs
F(timestamp__date)
(Pdb) p condition
Exact(F(timestamp__date), F(timestamp__date))

It looks like something in lines 647-654 is going wrong here when generating the list of filters to query the database with. In the buggy version with F('timestamp__date') of the constraint we’re generating a filter that is equivalent to this ORM query:

Timestamped.objects.filter(Exact(F('timestamp__date'), F('timestamp__date')))
# equivalent to:
Timestamped.objects.filter(timestamp__date=F('timestamp__date'))

Unsurprisngly this will always return every row in the table, and therefore cause the validation to fail. The version with TruncDate('timestamp') is producing the correct filter:

Timestamped.objects.filter(Exact(TruncDate(F('timestamp')), TruncDate(Value(datetime.datetime(2025, 2, 6, 9, 42, tzinfo=datetime.timezone.utc))))
# equivalent to:
Timestamped.objects.filter(timestamp__date=datetime.datetime(2025, 2, 6, 9, 42, tzinfo=datetime.timezone.utc).date())

Digging further into it it I think I might have found the culprit.

.venv/lib/python3.13/site-packages/django/db/models/expressions.py:897
class F(Combinable):

    def replace_expressions(self, replacements):
        return replacements.get(self, self)

The F object just looks for itself in the replacements dictionary. But the dictionary generated is for F('timestamp') not F('timestamp__date'), so the lookup fails and it returns itself.

Why does the replacements dictionary have the wrong key? Looking back at line 641 I think the problem is in the dictionary comprehension to build up the replacements dict.

            replacements = {
                F(field): value
                for field, value in instance._get_field_expression_map(
                    meta=model._meta, exclude=exclude
                ).items()
            }
            filters = []

It reconstructs the keys as F objects from the instances field expression map, which just returns the field names defined on the model and this is where we lose the definition with a lookup provided initially.

1 Like

Interestingly this implies that the constraint will fail to work properly even when it is the only expression, so I can reduce the regression test down to this simpler case:

class RegressionModel(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",
            ),
        ]

class TestRegressionModel(TestCase):
    def test_demonstrate_constraint_fails(self):
        a = RegressionModel.objects.create(timestamp=dt.datetime.today())
        b = RegressionModel(timestamp=dt.datetime.today() - dt.timedelta(days=3))
        b.full_clean() #ValidationError
        b.save() #success

That’s all a great find!

I think at this point it’s probably worth writing up a ticket for this. (I’d also suggest referencing this thread in addition to the basic information you put in the ticket itself.)

Thanks Ken, opened up #36198. Appreciate the guidance!