Removing db_index from a field drops an unchanged Meta.constraints UniqueConstraint on MySQL

[Tony S Yu]

Version: 6.1 and 4.2
Database: MySQL 8.4

On MySQL, removing db_index=True from a field that also has a named UniqueConstraint in Meta.constraints causes Django to drop both:

1. The non-unique index created by db_index=True.
2. The unique index backing the unchanged UniqueConstraint.

The model continues to contain the UniqueConstraint, but the database no longer enforces it since the index was dropped.

Side note: This is an unusual initial state for a Django model (specifically, having a field with a db_index with a UniqueConstraint for the same field), but there were reasons that caused our app to reach this state. There are other ways to implement this migration to avoid the issue described here, but that's separate from whether this should be considered a bug.

Reproduction

Step 1: Start with this model:

from django.db import models


class Person(models.Model):
    username = models.CharField(max_length=30, db_index=True)

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["username"],
                name="unique_username",
            ),
        ]

Step 2: Create and apply the migration:

$ python manage.py makemigrations
$ python manage.py migrate

MySQL now has two indexes on username:

• A non-unique index generated by db_index=True.
• A unique index named unique_username, backing the UniqueConstraint.

Step 3: Remove only db_index=True:

class Person(models.Model):
    username = models.CharField(max_length=30)

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["username"],
                name="unique_username",
            ),
        ]

Step 4: Generate another migration:

$ python manage.py makemigrations
$ python manage.py sqlmigrate app <migration_number>

Actual result

Django generates SQL equivalent to:

DROP INDEX `unique_username` ON `app_person`;
DROP INDEX `app_person_username_b909c738` ON `app_person`;

Both indexes are dropped, including the one enforcing the unchanged UniqueConstraint.

Expected result

Only the non-unique index created by db_index=True should be dropped:

DROP INDEX `app_person_username_b909c738` ON `app_person`;

The unique_username constraint/index should remain.

Analysis

Adapted from Sourcegraph Amp analysis

MySQL implements a unique constraint using a unique index. Consequently, DatabaseIntrospection.get_constraints() reports unique_username with both:

{
    "unique": True,
    "index": True,
    "type": "idx",
}

When handling removal of db_index, BaseDatabaseSchemaEditor._alter_field() searches for matching BTREE indexes:

meta_index_names = {index.name for index in model._meta.indexes}
index_names = self._constraint_names(
    model,
    [old_field.column],
    index=True,
    type_=Index.suffix,
    exclude=meta_index_names,
)

This excludes indexes declared in Meta.indexes, but not constraints declared in Meta.constraints. On MySQL, the unique index therefore matches the deletion query.

A possible fix would be to exclude names from both collections, or otherwise ensure that only non-unique indexes are selected:

meta_constraint_names = {
    constraint.name for constraint in model._meta.constraints
}
meta_index_names = {index.name for index in model._meta.indexes}

# ...
exclude=meta_constraint_names | meta_index_names

A regression test could alter a db_index=True field with a same-column UniqueConstraint, then assert that the ordinary index is removed while the named unique constraint remains.

Related tickets

• ticket:30172 fixed similar cases where altering a field's unique or check behavior accidentally removed unchanged constraints from Meta.constraints. The db_index removal branch appears not to have received the equivalent protection.
• ticket:31335 concerns indexes required to support foreign keys on MySQL. It is related to MySQL's overlapping constraint/index representation, but does not cover this case.

[Yassin Bahri]

I reproduced this on current main using MySQL 8.4.

This does not appear to be a regression, so I did not run git bisect.
The affected db_index removal logic predates support for
UniqueConstraint in Meta.constraints, introduced in Django 2.2 by
commit db13bca60a. The removal logic did not account for these constraints
when that feature was introduced.

Therefore, the issue appears to have existed since this model configuration
first became supported rather than being caused by a later behavioral
regression.

A schema-editor regression test creates both a field index and a same-column named UniqueConstraint, then removes db_index.

Without the proposed change, the test fails because the resulting constraints contain only PRIMARY; the named unique constraint is no longer present.

Excluding names from both model._meta.constraints and model._meta.indexes preserves the unique constraint while removing only the field-created index.

The focused regression test and the complete schema test suite pass on MySQL 8.4:

Ran 228 tests in 102.257s

OK (skipped=36)

I also ran the complete schema test suite on SQLite:

Ran 228 tests

OK (skipped=47)

This does not appear to be a recent regression. The affected db_index removal logic predates support for UniqueConstraint in Meta.constraints, which was introduced in Django 2.2. Therefore, there is no later regression commit to bisect.

Based on this reproduction, this appears to be a valid bug. I am marking the Triage Stage as Accepted.

[Yassin Bahri]

Patch submitted: ​PR

[Clifford Gama]

The proposed patch is straightforward and LGTM. Looks like 5c17c273ae2d7274f1fa78218b3b74690efddb86 in #30172 had overlooked this case.