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
35761	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,."	Bug	closed	Database layer (models, ORM)	4.2	Normal	duplicate	migrations, PK,		Unreviewed	0	0	0	0	0	0