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
34949	Clarify when unique constraints are ignored.	Roman Odaisky	Jordan Bae	"Consider the following code:
{{{
class SomeModel(models.Model):
    a = models.IntegerField()
    b = models.IntegerField()
    c = models.IntegerField()
    d = models.IntegerField()
    e = models.IntegerField()
    f = models.IntegerField()
    g = models.IntegerField(null=True)

    class Meta:
        indexes = [
            models.Index(fields=[""a""], name=""index_a""),
            models.Index(fields=[""b""], include=[""c""], name=""index_b_include_c""),
        ]
        constraints = [
            models.UniqueConstraint(fields=[""d""], name=""unique_d""),
            models.UniqueConstraint(fields=[""e""], include=[""f""], name=""unique_e_include_f""),
            models.UniqueConstraint(fields=[""g""], nulls_distinct=True, name=""unique_g_nulls_distinct""),
        ]
}}}

= Expected Behavior =

From the documentation:
* `include` is ignored for databases besides PostgreSQL.
* `nulls_distinct` is ignored for databases besides PostgreSQL 15+.

One would imagine that if we run this on SQLite we’ll end up with five indexes on one field each as though the unsupported parameters were not present. 

= Actual Behavior =

Instead we get:
* `index_a` on `a`
* `index_b_include_c` on `b`
* `unique_d` on `d`
* and **no** other constraints!

The documentation and the behavior are clearly at odds, and the behavior is very confusing. The intention behind indexes like `unique_e_include_f` is to enforce a constraint and to enable an optimization on top of that, Django is unable to provide the optimization so it doesn’t enforce the constraint either?

= Workflows Affected =

It seems to me there are two important workflows, none of which Django currently supports:
1. A project for which the DB has been chosen long ago, and a migration to a different one is very unlikely. The developer would like to get the most out of the DB and is willing to use DB-specific features. In this case Django should raise errors if the specific features requested are unavailable.
2. A DB-agnostic app intended to be included in other projects. The developer would like Django to create indexes that best match the performance requirements as appropriate for whatever DB the app may end up running on.

For example, in the second case, anticipating a lot of `SomeModel.objects.filter(b__range=...).values(""b"", ""c"")` the developer would like to create an `Index(fields=[""b""], include=[""c""])` but with a fallback to `Index(fields=[""b"", ""c""])` if covering indexes aren’t supported. The interface to create custom indexes is [https://docs.djangoproject.com/en/dev/ref/models/constraints/#baseconstraint in theory documented] but very sparsely (it doesn’t even say what the arguments to create_sql are) so expecting app developers to use this route is unrealistic.

Note that while in many other cases failing to perform an optimization is considered a graceful fallback, indexes are explicitly created for purposes of optimization and failing to achieve that is an error that should be signaled.

= Suggested Resolution =
1. Under no circumstances should Django quietly decline to create a unique constraint.
2. Index and UniqueConstraint classes should have extra parameters to specify what to fall back to if requested features are unavailable. Perhaps something along the lines of
{{{
models.Index(
    fields=[""b""],
    include=[""c""],
    fallback=models.Index(fields=[""b"", ""c""]),
    # where does the name go though?
)
models.UniqueConstraint(
    fields=[""b""],
    include=[""c""],
    fallback=[
        models.UniqueConstraint(fields=[""b""]),
        models.Index(fields=[""b"", ""c""]),
    ],
)
models.UniqueConstraint(
    fields=[""x""],
    nulls_distinct=True,
    fallback=models.UniqueConstraint(Coalesce(F(""x""), 0)),
)
}}}
3. There should be a setting to make it a warning or an error if Django encounters an index that it can’t create exactly and that has no user-specified fallback.
4. A perfect solution would also provide a way of upgrading indexes if a later version of the DB adds support for the features that were previously missing.

Here’s my attempt to approximate a solution:
{{{
class FallbackIndex(models.Index):
    def __init__(self, *a, requires=None, fallback=None, **k):
        super().__init__(*a, **k)
        self.requires = requires
        self.fallback = fallback

    def create_sql(self, model, schema_editor, using="""", **kwargs):
        return (
            super()
            if getattr(schema_editor.connection.features, self.requires)
            else self.fallback
        ).create_sql(model, schema_editor, using=using, **kwargs)

    def deconstruct(self):
        path, expressions, kwargs = super().deconstruct()
        kwargs[""requires""] = self.requires
        kwargs[""fallback""] = self.fallback
        return path, expressions, kwargs

indexes = [
    FallbackIndex(
        fields=[""b""],
        include=[""c""],
        name=""index_b_include_c"",
        requires=""supports_covering_indexes"",
        fallback=models.Index(fields=[""b"", ""c""], name=""index_b_include_c""),
    ),
]
}}}
except it should be possible to determine the required features automatically."	Cleanup/optimization	closed	Documentation	dev	Normal	fixed		Jordan Bae	Ready for checkin	1	0	0	0	0	0