Incorrect warning when specifying a UniqueConstraint with SQLite and nulls_distinct=False

[Russell Owen]

I am trying to create a UniqueConstraint with nulls_distinct=False. This will be used with sqlite for local use and tests, and postgresql for production.

When I migrate my sqlite database I get this warning:

SQLite does not support unique constraints with nulls distinct.

I suspect this is a bug, since I specified that nulls should not be distinct.

Note that I also get the same warning if I specify nulls_distinct=True, but in that case I expect to see it.

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 full warning I see on migration is:

WARNINGS:
dances.SimpleModel: (models.W047) SQLite does not support unique constraints with nulls distinct.
	HINT: A constraint won't be created. Silence this warning if you don't care about it.

and, as I said before, I get the same warning if I use nulls_distinct=True (which is when I expect to see the warning).

[Tim Graham]

A problem with your proposal is that the behavior of whether or not nulls are distinct is database dependent. In fact, the behavior of SQLite is the opposite of what you stated: null values are considered distinct. The docs for ​UniqueConstraint.nulls_distinct says: "The default value is None which uses the database default which is True on most backends."

I don't think there's a strong argument to make this check aware of the default treatment of nulls. Using a different database in testing and production is highly advised against. Indeed, your UniqueConstraint example highlights a feature of PostgreSQL that SQLite doesn't support.

[Russell Owen]

I feel that you did not understand the point of my bug report (it's definitely not meant to be a proposal) and have closed it prematurely and unfairly.

The bug is that one cannot construct a UniqueConstraint in sqlite if one specifies a boolean value for the nulls_distinct argument, even if the boolean value matches the behavior that sqlite supports. The only accepted value is None, and that is needlessly limiting.

Based on the warning message that is printed, I believe that an explicit value of False should be accepted. If I misunderstand and got the sign wrong, the bug still should be fixed. The *compatible* boolean value of nulls_distinct should be acceptable. It should result in the constraint being generated, without any warning.

Use cases for specifying an explicit boolean value for nulls_distinct include:

• Clarity: Maintainers can see the intended behavior.
• Predictability. The behavior is known, instead of being a mysterious database-specific default.
• Portability. The code will behave the same way when used with a different database (provided that database actually supports the specified mode).