Optimize SchemaEditor.alter_field() to avoid dropping foreign key constraints if not needed

[Cameron Dawson]

If you just add a related_name to an existing foreign key, it will require a migration which then drops and re-creates the foreign key. This is a non-DB, meta change only and shouldn't create a migration. This is with MySQL.

With Models:

class Job(models.Model):
    id = models.BigAutoField(primary_key=True)
    name = models.CharField(max_length=30, null=True)
  
class JobDetail(models.Model):
    id = models.BigAutoField(primary_key=True)
    job = models.ForeignKey(Job)

my initial migration is:

operations = [
    migrations.CreateModel(
        name='Job',
        fields=[
            ('id', models.BigAutoField(primary_key=True, serialize=False)),
            ('name', models.CharField(max_length=30, null=True)),
        ],
    ),
    migrations.CreateModel(
        name='JobDetail',
        fields=[
            ('id', models.BigAutoField(primary_key=True, serialize=False)),
            ('job', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='polls.Job')),
        ],
    ),
]

Then if I modify the JobDetail model ForeignKey as follows:

job = models.ForeignKey(Job, related_name="job_details")

and run makemigrations, I end up with a migration:

operations = [
    migrations.AlterField(
        model_name='jobdetail',
        name='job',
        field=models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='job_details', to='polls.Job'),
    ),
]

The CREATE_INDEX result in sqlmigrate for both migrations is identical:

CREATE INDEX "polls_jobdetail_job_id_5561ba44" ON "polls_jobdetail" ("job_id");

We need to work around it with migrations.RunSQL.noop because the tables in our actual application are really large and dropping the index and re-creating is very expensive.

[Tim Graham]

The migration is needed as ​explained in the documentation:

Django will make migrations for any change to your models or fields - even options that don’t affect the database - as the only way it can reconstruct a field correctly is to have all the changes in the history, and you might need those options in some data migrations later on (for example, if you’ve set custom validators).

but possibly the operation could be optimized so that SQL isn't executed for changes to non-database options. This is a duplicate of #25253.

Incidentally, the output for AlterField I see is:

ALTER TABLE `t28065_jobdetail` DROP FOREIGN KEY `t28065_jobdetail_job_id_7adae3b8_fk_t28065_job_id`;
ALTER TABLE `t28065_jobdetail` ADD CONSTRAINT `t28065_jobdetail_job_id_7adae3b8_fk_t28065_job_id` FOREIGN KEY (`job_id`) REFERENCES `t28065_job` (`id`);

Nothing about CREATE INDEX as you mentioned in the ticket. Not sure if you might be using a third-party MySQL backend or something.