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
37286	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:

{{{#!python
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:

{{{#!console
$ 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`:

{{{#!python
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:

{{{#!console
$ python manage.py makemigrations
$ python manage.py sqlmigrate app <migration_number>
}}}

== Actual result ==

Django generates SQL equivalent to:

{{{#!sql
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:

{{{#!sql
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:

{{{#!python
{
    ""unique"": True,
    ""index"": True,
    ""type"": ""idx"",
}
}}}

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

{{{#!python
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:

{{{#!python
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.
"	Bug	new	Migrations	6.1	Normal			Tony S Yu	Unreviewed	0	0	0	0	0	0