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 31335 Renaming a ForeignKey included in an index, and removing UniqueConstraint, Index with ForeignKey fails on MySQL. Stephen Finucane Sergey Fursov "I have a foreign key field on a model that I wish to rename. Unfortunately that field is included in an index. Attempting to rename this generates the following error. {{{ MySQLdb._exceptions.OperationalError: (1553, ""Cannot drop index 'foo_idx': needed in a foreign key constraint"") }}} I've included reproduction steps along with a workaround below. This is an issue on both 3.0.3 and 1.11.28. ---- Create a sample project and application: {{{ $ virtualenv venv $ pip install django mysqlclient $ django-admin startproject test $ cd test/ $ django-admin startapp core }}} Configure `test/settings.py` to use the `mysql` backend and enable the `core` app, then create some sample models: {{{ cat <<< EOF > core/models.py import datetime from django.db import models class Article(models.Model): name = models.CharField(max_length=255, blank=True, null=False) date = models.DateTimeField(default=datetime.datetime.utcnow) body = models.TextField(blank=True, null=False) class Comment(models.Model): article = models.ForeignKey( Article, related_name='comments', related_query_name='comment', on_delete=models.CASCADE, ) date = models.DateTimeField(default=datetime.datetime.utcnow) body = models.TextField(blank=True, null=False) class Meta: indexes = [ # This is a covering index for the /list/ query models.Index( fields=['article', 'date'], name='comment_list_covering_idx', ), ] EOF }}} Once done, create and apply the migrations, then create some entries: {{{ $ python manage.py makemigrations $ python manage.py migrate $ python manage.py shell >>> from core.models import Article, Comment >>> article_a = Article(name='Sample article', body='foo').save() >>> article_b = Article(name='Another article', body='bar').save() >>> comment_1 = Comment(article=article_a) >>> comment_2 = Comment(article=article_b, body='moar stuff') }}} Rename the foreign key field, for example from `article` to `foo`, updating the index in the process, and generate the migration: {{{ $ python manage.py makemigrations Did you rename comment.article to comment.foo (a ForeignKey)? [y/N] y Migrations for 'core': core/migrations/0002_auto_20200303_1424.py - Remove index comment_list_covering_idx from comment - Rename field article on comment to foo - Create index comment_list_covering_idx on field(s) foo, date of model comment }}} Attempt to run that migration. It will fail: {{{ $ python manage.py migrate Operations to perform: Apply all migrations: admin, auth, contenttypes, core, sessions Running migrations: Applying core.0002_auto_20200303_1424...Traceback (most recent call last): File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/backends/utils.py"", line 86, in _execute return self.cursor.execute(sql, params) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/backends/mysql/base.py"", line 74, in execute return self.cursor.execute(query, args) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/MySQLdb/cursors.py"", line 209, in execute res = self._query(query) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/MySQLdb/cursors.py"", line 315, in _query db.query(q) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/MySQLdb/connections.py"", line 239, in query _mysql.connection.query(self, query) MySQLdb._exceptions.OperationalError: (1553, ""Cannot drop index 'comment_list_covering_idx': needed in a foreign key constraint"") The above exception was the direct cause of the following exception: Traceback (most recent call last): File ""manage.py"", line 21, in main() File ""manage.py"", line 17, in main execute_from_command_line(sys.argv) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/core/management/__init__.py"", line 401, in execute_from_command_line utility.execute() File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/core/management/__init__.py"", line 395, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/core/management/base.py"", line 328, in run_from_argv self.execute(*args, **cmd_options) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/core/management/base.py"", line 369, in execute output = self.handle(*args, **options) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/core/management/base.py"", line 83, in wrapped res = handle_func(*args, **kwargs) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/core/management/commands/migrate.py"", line 233, in handle fake_initial=fake_initial, File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/migrations/executor.py"", line 117, in migrate state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/migrations/executor.py"", line 147, in _migrate_all_forwards state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/migrations/executor.py"", line 245, in apply_migration state = migration.apply(state, schema_editor) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/migrations/migration.py"", line 124, in apply operation.database_forwards(self.app_label, schema_editor, old_state, project_state) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/migrations/operations/models.py"", line 783, in database_forwards schema_editor.remove_index(model, index) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/backends/base/schema.py"", line 356, in remove_index self.execute(index.remove_sql(model, self)) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/backends/base/schema.py"", line 142, in execute cursor.execute(sql, params) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/backends/utils.py"", line 100, in execute return super().execute(sql, params) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/backends/utils.py"", line 68, in execute return self._execute_with_wrappers(sql, params, many=False, executor=self._execute) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/backends/utils.py"", line 77, in _execute_with_wrappers return executor(sql, params, many, context) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/backends/utils.py"", line 86, in _execute return self.cursor.execute(sql, params) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/utils.py"", line 90, in __exit__ raise dj_exc_value.with_traceback(traceback) from exc_value File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/backends/utils.py"", line 86, in _execute return self.cursor.execute(sql, params) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/django/db/backends/mysql/base.py"", line 74, in execute return self.cursor.execute(query, args) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/MySQLdb/cursors.py"", line 209, in execute res = self._query(query) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/MySQLdb/cursors.py"", line 315, in _query db.query(q) File ""/tmp/django-bug/venv/lib/python3.7/site-packages/MySQLdb/connections.py"", line 239, in query _mysql.connection.query(self, query) django.db.utils.OperationalError: (1553, ""Cannot drop index 'comment_list_covering_idx': needed in a foreign key constraint"") }}} The only workaround I've seen is to use `AlterField` to remove the fk constraint on the `article` field before doing this work, then use `AlterField` again after to re-add it. Perhaps that needs to happen under the hood if MySQL can't do this? Output of pip freeze and the migrations below: {{{ $ pip freeze asgiref==3.2.3 Django==3.0.3 mysqlclient==1.4.6 pytz==2019.3 sqlparse==0.3.1 }}} {{{ $ cat core/migrations/0001_initial.py # Generated by Django 3.0.3 on 2020-03-03 14:22 import datetime from django.db import migrations, models import django.db.models.deletion class Migration(migrations.Migration): initial = True dependencies = [ ] operations = [ migrations.CreateModel( name='Article', fields=[ ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')), ('name', models.CharField(blank=True, max_length=255)), ('date', models.DateTimeField(default=datetime.datetime.utcnow)), ('body', models.TextField(blank=True)), ], ), migrations.CreateModel( name='Comment', fields=[ ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')), ('date', models.DateTimeField(default=datetime.datetime.utcnow)), ('body', models.TextField(blank=True)), ('article', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='comments', related_query_name='comment', to='core.Article')), ], ), migrations.AddIndex( model_name='comment', index=models.Index(fields=['article', 'date'], name='comment_list_covering_idx'), ), ] }}} {{{ $ cat core/migrations/0002_auto_20200303_1424.py # Generated by Django 3.0.3 on 2020-03-03 14:24 from django.db import migrations, models class Migration(migrations.Migration): dependencies = [ ('core', '0001_initial'), ] operations = [ migrations.RemoveIndex( model_name='comment', name='comment_list_covering_idx', ), migrations.RenameField( model_name='comment', old_name='article', new_name='foo', ), migrations.AddIndex( model_name='comment', index=models.Index(fields=['foo', 'date'], name='comment_list_covering_idx'), ), ] }}}" Bug closed Migrations 3.0 Normal fixed Perry Harrington David Wobrock Ready for checkin 1 0 0 0 0 0