How to write migration to change primary key of model with ManyToManyField

Question

I have a UserProfile model that refers to my User model with a OneToOneField. I also use the post_save signal to auto create the UserProfile when the user is created. This works great apart from when creating a user through the admin (where I use an inline) when I get an error about duplicate profile. This answer recommends setting the primary key to be the OneToOneField referring to user.

So before:

class UserProfile(models.Model):
    user = models.OneToOneField(settings.AUTH_USER_MODEL)
    # ...
    subjects = models.ManyToManyField(Subject, null=True, blank=True)

After

class UserProfile(models.Model):
    user = models.OneToOneField(settings.AUTH_USER_MODEL, primary_key=True)
    # ...
    subjects = models.ManyToManyField(Subject, null=True, blank=True)

I'm trying to do that using the migrations in Django 1.7, but life is complicated by the fact that the profile has a number of ManyToManyField - so they all refer to the id field of the UserProfile model. Using makemigrations creates the migrations for making the user the primary key, and dropping the old id field, but it ignores the ManyToManyField.

I'm currently going down a rabbit hole of using lots of RunSQL statements in the migration to modify the through table for the ManyToManyField. I've just hit another error where the name of the constraint is not the same in one table as another.

So my question is: is there a method in Django migrations that will do the work of changing the through table so it refers to the new primary key, updating all the constraints, keys etc? If not, what's the best way to handle this situation?

I'm using Django 1.7 with MySQL.

Branchia

I'd make the argument that the solution is incorrect. Your signal should be amended to not attempt to crate duplicate profiles. The primary key solution just seems hacky to me. – Pout

@Pout - the signal handler creates the profile before the admin inline tries to create a profile. So the signal handler would have to work out the user was being created by the admin in order to know that the admin would then try to create a user - it can't simply test to see if the profile already exists in the database. The alternative would be to have the admin inline test if the profile already exists before creating it, but that feels hacky to me. – Branchia

@HamishDowner looks like its a bug: code.djangoproject.com/ticket/25012 – Cioban

Answer 1

So I ended up with SQL to fix it. The core of my solution is below - basically I

• create an index on user_id in the new profile
  • this index needs to exist before I can reference it as a foreign key
• create a new through table
  • I started with the output of SHOW CREATE TABLE userprofile_userprofile_subjects (MySQL specific)
  • I modified the key names and constraint names slightly
• copy all the data into the new through table
• drop the old through table
• rename the new through table to have the name of the old through table
• finally do the operations that django migrations automatically generated for me

I hope this helps someone else. And I'd still be interested to know about a better solution.

from django.db import migrations

class Migration(migrations.Migration):

    dependencies = [
        # ...
    ]

    operations = [
        migrations.RunSQL(
            'ALTER TABLE userprofile_userprofile '
            'ADD INDEX `userprofile_userprofile_1234abcd` (user_id)'
        ),
        migrations.RunSQL (
            'CREATE TABLE userprofile_temp_table ('
            '`id` int(11) NOT NULL AUTO_INCREMENT, '
            '`userprofile_id` int(11) NOT NULL, '
            '`subject_id` int(11) NOT NULL, '
            'PRIMARY KEY (`id`), '
            'UNIQUE KEY `userprofile_userprofile_subjects_userprofile_us_7ded3060_uniq` (`userprofile_id`,`subject_id`), '
            'KEY `userprofile_userprofile_subject_1be9924f` (`userprofile_id`), '
            'KEY `userprofile_userprofile_subject_e5a9504a` (`subject_id`), '
            'CONSTRAINT `subject_id_refs_id_69796996` FOREIGN KEY (`subject_id`) REFERENCES `otherapp_subject` (`id`), '
            'CONSTRAINT `userprofile_user_id_refs_user_id_1234abcd` FOREIGN KEY (`userprofile_id`) REFERENCES `userprofile_userprofile` (`user_id`) '
            ') ENGINE=InnoDB AUTO_INCREMENT=35500 DEFAULT CHARSET=utf8 '
        ),
        migrations.RunSQL (
            'INSERT INTO userprofile_temp_table '
            '(userprofile_id, subject_id) '
            '('
            '  SELECT userprofile_userprofile.user_id, userprofile_userprofile_subjects.subject_id'
            '    FROM userprofile_userprofile_subjects'
            '    INNER JOIN userprofile_userprofile'
            '    ON userprofile_userprofile_subjects.userprofile_id ='
            '        userprofile_userprofile.id'
            ')'
        ),
        migrations.RunSQL (
            'DROP TABLE `userprofile_userprofile_subjects`'
        ),
        migrations.RunSQL (
            'RENAME TABLE `userprofile_temp_table` TO `userprofile_userprofile_subjects`'
        ),
        migrations.RemoveField(
            model_name='userprofile',
            name='id',
        ),
        migrations.AlterField(
            model_name='userprofile',
            name='user',
            field=models.OneToOneField(
                primary_key=True, serialize=False, to=settings.AUTH_USER_MODEL
            ),
            preserve_default=True,
        ),
    ]

Branchia

Thanks @Hamish - your solution helped me decide not to use a custom primary key with models having m2m fields :) Faced a few bugs on Django dealing with that (#25012, #24030, #22997). Details here. – Cioban