When switching which field is `primary_key`, migrations do not seem to handle multiple `auto` columns

[Jonas Vacek]

When there's a field that's defined as a primary key, switching to a different field (or the default id) as the primary key does not work on MySQL.

effectively, when going via the following states...

class MyModel(models.Model):
   user_id = models.OneToOneField(primary_key=True)
   other_field=models.CharField(...)

→

class MyModel(models.Model):
   user_id = models.OneToOneField(primary_key=True)
   id = models.BigIntegerField()

...generating this migration, and copying the values of bad_key to id in the migration...

...and then swapping the primary_key separately...

class MyModel(models.Model):
   user_id = models.OneToOneField()
   id = models.AutoField(primary_key=True) # Removed after migrations

Generates the following migration operations (across two files, condensed for brevity)

#1
migrations.AddField(
    model_name="lookeruser",
    name="id",
    field=models.BigIntegerField(default=0),
),
#2
migrations.RunPython(move_pks_to_new_id),
# -- new file --
# 3
migrations.AlterField(
    model_name="lookeruser",
    name="id",
    field=models.BigAutoField(auto_created=True, primary_key=True, serialize=False, verbose_name="ID"),
),
# 4
migrations.AlterField(
    model_name="lookeruser",
    name="user_id",
    field=models.OneToOneField(
        help_text="The user on our platform.",
        on_delete=django.db.models.deletion.CASCADE,
        related_name="looker_user",
        to=settings.AUTH_USER_MODEL,
    ),
),

This results in the following

E       django.db.utils.OperationalError: (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')

Swapping the order of 3 and 4 does not seem to make a difference here, they generate the following SQL for mysql:

--
-- Alter field id on lookeruser
--
ALTER TABLE `custom_reporting_lookeruser` MODIFY `id` bigint AUTO_INCREMENT NOT NULL;
ALTER TABLE `custom_reporting_lookeruser` ADD CONSTRAINT `custom_reporting_lookeruser_id_14e613d0_pk` PRIMARY KEY (`id`);
--
-- Alter field user on lookeruser
--
ALTER TABLE `custom_reporting_lookeruser` DROP FOREIGN KEY `custom_reporting_lookeruser_user_id_d2546465_fk_auth_user_id`;
ALTER TABLE `custom_reporting_lookeruser` DROP PRIMARY KEY;
ALTER TABLE `custom_reporting_lookeruser` ADD CONSTRAINT `custom_reporting_lookeruser_user_id_d2546465_uniq` UNIQUE (`user_id`);
ALTER TABLE `custom_reporting_lookeruser` ADD CONSTRAINT `custom_reporting_lookeruser_user_id_d2546465_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);

I would have assumed this would be possible this usecase would have been possible to achieve with the ORM, but I might be wrong..?