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
32386	ForeignKey index is removed upon creation of a UniqueConstraint, which breaks migrating backwards	Tijmen	nobody	"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'),
        ),
    ]

}}}

"	Bug	closed	Migrations	3.1	Normal	duplicate	backward migration unique constraint foreignkey index	tijmen@…	Unreviewed	0	0	0	0	0	0