Allow UniqueConstraint's nulls_distinct to accept a database's default value rather than raising a system check error

[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).

[Tim Graham]

I understand your proposal, but it's not how nulls_distinct was designed to be used. The documentation states, "Unique constraints with nulls_distinct are ignored for databases besides PostgreSQL 15+." Your proposal to change the behavior to make the attribute a flag that has some relation to a database's default treatment of null values is a new feature rather than a bug. For new features, you need to find consensus on the DevelopersMailingList. If there is consensus to change the behavior, we'll reopen the ticket.

A concrete use case would help your argument. I'm skeptical that a user defining:

UniqueConstraint(
    name="simple_unique_constraint",
    fields=["field_a", "field_b"],
    nulls_distinct=False,
)

should mean "I want a unique constraint, but only on databases where nulls aren't treated as distinct."

[Russell Owen]

Thank you. I had missed that. The warning message is still misleading, however, since it complains even when the boolean value of the argument matches the behavior of sqlite according to the message.

So I think this is a legitimate bug, but the bug is only that the warning message is misleading. If boolean values are not acceptable for a given db then the message should say that so the user knows how to fix the problem. I filed #36594 so this one can be closed.