#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:
> 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..?
New description:
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,.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/35761#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070191ebfaa9db-191bd0d9-22d0-4219-979e-1d0317968ce2-000000%40eu-central-1.amazonses.com.
