[Django] #36594: UniqueConstraint with a boolean value of nulls_distinct causes misleading warning for sqlite (and likely other dbs)

1 view

Skip to first unread message

Django

unread,

Sep 4, 2025, 10:01:35 AM (3 days ago) Sep 4

to django-...@googlegroups.com

#36594: UniqueConstraint with a boolean value of nulls_distinct causes misleading
warning for sqlite (and likely other dbs)
-------------------------------------+-------------------------------------
Reporter: Russell Owen | Type:
| Uncategorized
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | Severity: Normal
Keywords: UniqueConstraint | Triage Stage:
nulls_distinct | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
I was trying to create a UniqueConstraint with `nulls_distinct=False`
using sqlite. When I migrated my sqlite database I got this warning:
{{{
SQLite does not support unique constraints with nulls distinct.
}}}
That message implies that the value of False should work, so it is
misleading.

It turns out that boolean values of nulls_distinct are only acceptable for
postgresql, which I missed when I first read the docs. It would be very
helpful if the warning message included the information that boolean
values of nulls_distinct are not acceptable for sqlite (or whatever
database is triggering the warning).

Having the warning also include the information about the actual behavior
is a nice touch (especially as the docs say that the behavior is for nulls
to be distinct in most dbs), but the message should also include the root
cause of the warning. That way the user knows how to fix the problem.

Here is the simplest model I could come up with that shows the issue:
{{{
class SimpleModel(models.Model):
field_a = models.IntegerField(null=True)
field_b = models.IntegerField(null=True)

class Meta:
constraints = [
UniqueConstraint(
name="simple_unique_constraint",
fields=["field_a", "field_b"],
nulls_distinct=False,
),
]
}}}
(The same warning is printed whether nulls_distinct is False or True,
which is why it is so confusing.)

It would be also nice to emphasize the limitation of `nulls_distinct` in
the documentation by making it warning or putting it in bold as the first
line of the description of the argument.
--
Ticket URL: <https://code.djangoproject.com/ticket/36594>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Sep 4, 2025, 10:02:38 AM (3 days ago) Sep 4

to django-...@googlegroups.com

#36594: UniqueConstraint with a boolean value of nulls_distinct causes misleading
warning for sqlite (and likely other dbs)
-------------------------------------+-------------------------------------

Description changed by Russell Owen:

Old description:

New description:

I was trying to create a UniqueConstraint with `nulls_distinct=False`
using sqlite. When I migrated my sqlite database I got this warning:
{{{
SQLite does not support unique constraints with nulls distinct.
}}}
That message implies that the value of False should work, so it is
misleading.

It turns out that boolean values of nulls_distinct are only acceptable for
postgresql, which I missed when I first read the docs. It would be very
helpful if the warning message included the information that boolean
values of nulls_distinct are not acceptable for sqlite (or whatever

database is triggering the warning) so the user knows how to fix the
problem.

Having the warning also include the information about the actual behavior

is a nice touch, but is not the heart of the problem.

Here is the simplest model I could come up with that shows the issue:
{{{
class SimpleModel(models.Model):
field_a = models.IntegerField(null=True)
field_b = models.IntegerField(null=True)

class Meta:
constraints = [
UniqueConstraint(
name="simple_unique_constraint",
fields=["field_a", "field_b"],
nulls_distinct=False,
),
]
}}}
(The same warning is printed whether nulls_distinct is False or True,
which is why it is so confusing.)

It would be also nice to emphasize the limitation of `nulls_distinct` in
the documentation by making it warning or putting it in bold as the first
line of the description of the argument.

--