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')

operations 3+4 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`);

Swapping their order does not seem to fix the issue.

There are no other indexes or anything else set up on this table.

I was hoping that removing primary_key=True was a usecase covered by the ORM,.

[Jonas Vacek]

My workaround for this was to Swap operations 3 and 4, and change the order of the alter tables in the primary key addition

    operations: list[Operation] = [
        migrations.AlterField(
            model_name="lookeruser",
            name="user",
            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,
            ),
        ),
        migrations.RunSQL(
            sql="""
                ALTER TABLE `custom_reporting_lookeruser` ADD CONSTRAINT `custom_reporting_lookeruser_id_14e613d0_pk` PRIMARY KEY (`id`);
                ALTER TABLE `custom_reporting_lookeruser` MODIFY `id` bigint AUTO_INCREMENT NOT NULL;
                """,
            state_operations=[
                migrations.AlterField(
                    model_name="lookeruser",
                    name="id",
                    field=models.BigAutoField(auto_created=True, primary_key=True, serialize=False, verbose_name="ID"),
                ),
            ],
        ),
    ]

[Sarah Boyce]

Thank you for the report! I think this is a duplicate of #22997

[Jonas Vacek]

Looks like it indeed, thanks for the follow up!