#35761 closed Bug (duplicate)
When switching which field is `primary_key`, migrations do not seem to handle multiple `auto` columns
Reported by: | Jonas Vacek | Owned by: | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.2 |
Severity: | Normal | Keywords: | migrations, PK, |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
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,.
Change History (4)
comment:1 by , 8 weeks ago
Description: | modified (diff) |
---|
comment:2 by , 8 weeks ago
comment:3 by , 7 weeks ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Thank you for the report! I think this is a duplicate of #22997
My workaround for this was to Swap operations 3 and 4, and change the order of the alter tables in the primary key addition