[Django] #35761: When switching which field is `primary_key`, migrations do not seem to handle multiple `auto` columns

[Django]

#35761: When switching which field is `primary_key`, migrations do not seem to
handle multiple `auto` columns
-------------------------------------+-------------------------------------
Reporter: Jonas Vacek | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 4.2 | Severity: Normal
Keywords: migrations, PK, | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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..?
--
Ticket URL: <https://code.djangoproject.com/ticket/35761>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35761: When switching which field is `primary_key`, migrations do not seem to
handle multiple `auto` columns
-------------------------------------+-------------------------------------

Reporter: Jonas Vacek | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: migrations, PK, | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Jonas Vacek:

Old description:

New description:

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,.

--
--
Ticket URL: <https://code.djangoproject.com/ticket/35761#comment:1>

[Django]

#35761: When switching which field is `primary_key`, migrations do not seem to
handle multiple `auto` columns
-------------------------------------+-------------------------------------
Reporter: Jonas Vacek | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: migrations, PK, | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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"),
),

],
),
]

}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35761#comment:2>

[Django]

#35761: When switching which field is `primary_key`, migrations do not seem to
handle multiple `auto` columns
-------------------------------------+-------------------------------------
Reporter: Jonas Vacek | Owner: (none)

Type: Bug | Status: closed

Component: Database layer | Version: 4.2
(models, ORM) |

Severity: Normal | Resolution: duplicate

Keywords: migrations, PK, | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* resolution: => duplicate
* status: new => closed

Comment:

Thank you for the report! I think this is a duplicate of #22997
--
Ticket URL: <https://code.djangoproject.com/ticket/35761#comment:3>

[Django]

#35761: When switching which field is `primary_key`, migrations do not seem to
handle multiple `auto` columns
-------------------------------------+-------------------------------------
Reporter: Jonas Vacek | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: migrations, PK, | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jonas Vacek):

Looks like it indeed, thanks for the follow up!
--
Ticket URL: <https://code.djangoproject.com/ticket/35761#comment:4>