ForeignKey index is removed upon creation of a UniqueConstraint, which breaks migrating backwards

[Tijmen]

When adding a UniqueConstraint on a ForeignKey field combined with another field, the original db index on the ForeignKey is removed and replaced by the unique constraint.
This crashes the migration when trying to migrate backwards, because the foreignkey needs to have an index.

django.db.utils.OperationalError: (1553, "Cannot drop index 'modelb_unique_position': needed in a foreign key constraint")

It makes no difference whether the unique constraint is added in the same or a separate migration.
In the example below it's separated into 2 different migrations.

Expect behavior would be either for the index not be dropped when creating the unique constraint, or for the index to be added before removing the unique constraint when migrating backwards.

First migration:

from django.db import migrations, models
import django.db.models.deletion


class Migration(migrations.Migration):

    dependencies = [
        ('appname', '0001_initial'),
    ]

    operations = [
        migrations.CreateModel(
            name='ModelA',
            fields=[
                ('id', models.AutoField(
                    auto_created=True,
                    primary_key=True,
                    serialize=False,
                    verbose_name='ID')),
            ],
        ),
        migrations.CreateModel(
            name='ModelB',
            fields=[
                ('id', models.AutoField(
                    auto_created=True,
                    primary_key=True,
                    serialize=False,
                    verbose_name='ID')),
                ('a_instance', models.ForeignKey(
                    on_delete=django.db.models.deletion.PROTECT,
                    related_name='bees',
                    to='appname.modela',
                    verbose_name=('modela',))),
                ('position', models.IntegerField(
                    default=0,
                    null=True,
                    verbose_name='volgorde')),
            ],
        ),
    ]

second migration:

from django.db import migrations, models
import django.db.models.deletion


class Migration(migrations.Migration):

    dependencies = [
        ('appname', '0002_modela_modelb'),
    ]

    operations = [
        migrations.AddConstraint(
            model_name='modelb',
            constraint=models.UniqueConstraint(
                fields=('a_instance', 'position'),
                name='modelb_unique_position'),
        ),
    ]

[Simon Charette]

I assume you are using MySQL, if it's the case then that's a duplicate of #31335, see https://code.djangoproject.com/ticket/#comment:2.