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

_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.