How to migrate from custom primary key to default id

Question

I have created a model with email address as custom primary key as follows:

email = models.EmailField(max_length=255, primary_key=True,)

Now I realized that this is not a good idea in my case and I would like to go back to the automatically generated id field as primary key.

How to do this? I tried this in different ways but all failed. I am using Django 1.10.4 with Python 3.4.3 with an SQLite database.

1. I just replaced the primary_key=True option by unique=True. python manage.py makemigrations complains:

You are trying to add a non-nullable field 'id' to user without a default; we can't do that (the database needs something to populate existing rows).

If I specify 0 as default value, python manage.py migrate fails with django.db.utils.IntegrityError: UNIQUE constraint failed: login_user.id

2. Based on this post Change Primary Key field to unique field I tried to add an Autofield manually, as in:

   id = models.AutoField()

Now python manage.py makemigrations fails with:

login.User.id: (fields.E100) AutoFields must set primary_key=True.

If I do as suggested by the error message, I get the same issue as in my first try: missing default value.

3. I tried to make a field id=IntegerField(unique=True) (following Django documentation at https://docs.djangoproject.com/en/1.10/howto/writing-migrations/#migrations-that-add-unique-fields) and then change the field type to AutoField(primary_key=True). At the same time, I need to change the email field to unique=True to avoid having two primary keys. After these changes, makemigrations works fine but migrate fails with a traceback and this error: django.db.utils.OperationalError: duplicate column name: id It seems to be trying to make an additional 'id' column, don't know why.

What is the correct way to do this? Also, if it succeeds, will ForeignKey fields that refer to my User be updated correctly?

Answer 1

I have come across this problem myself and ended up writing a reusable (MySQL-specific, though) migration. You can find the code in this repo. I've also written about it in my blog.

As a summary, the steps taken are:

1. Modify your model class like this:

   class Something(models.Model):
       email = models.EmailField(max_length=255, unique=True)
   

2. Add a new migration along these lines:

   app_name = 'app'
   model_name = 'something'
   related_model_name = 'something_else'
   model_table = '%s_%s' % (app_name, model_name)
   pivot_table = '%s_%s_%ss' % (app_name, related_model_name, model_name)
   fk_name, index_name = None, None 
   
   
   class Migration(migrations.Migration):
   
       operations = [
           migrations.AddField(
               model_name=model_name,
               name='id',
               field=models.IntegerField(null=True),
               preserve_default=True,
           ),
           migrations.RunPython(do_most_of_the_surgery),
           migrations.AlterField(
               model_name=model_name,
               name='id',
               field=models.AutoField(
                   verbose_name='ID', serialize=False, auto_created=True,
                   primary_key=True),
               preserve_default=True,
           ),
           migrations.AlterField(
               model_name=model_name,
               name='email',
               field=models.EmailField(max_length=255, unique=True),
               preserve_default=True,
           ),
           migrations.RunPython(do_the_final_lifting),
       ]
   

   where

   def do_most_of_the_surgery(apps, schema_editor):
       models = {}
       Model = apps.get_model(app_name, model_name)
   
       # Generate values for the new id column
       for i, o in enumerate(Model.objects.all()):
           o.id = i + 1
           o.save()
           models[o.email] = o.id
   
       # Work on the pivot table before going on
       drop_constraints_and_indices_in_pivot_table()
   
       # Drop current pk index and create the new one
       cursor.execute(
           "ALTER TABLE %s DROP PRIMARY KEY" % model_table
       )
       cursor.execute(
           "ALTER TABLE %s ADD PRIMARY KEY (id)" % model_table
       )
   
       # Rename the fk column in the pivot table
       cursor.execute(
           "ALTER TABLE %s "
           "CHANGE %s_id %s_id_old %s NOT NULL" %
           (pivot_table, model_name, model_name, 'VARCHAR(255)'))
       # ... and create a new one for the new id
       cursor.execute(
           "ALTER TABLE %s ADD COLUMN %s_id INT(11)" %
           (pivot_table, model_name))
   
       # Fill in the new column in the pivot table
       cursor.execute("SELECT id, %s_id_old FROM %s" % (model_name, pivot_table))
       for row in cursor:
           id, key = row[0], row[1]
           model_id = models[key]
   
           inner_cursor = connection.cursor()
           inner_cursor.execute(
               "UPDATE %s SET %s_id=%d WHERE id=%d" %
               (pivot_table, model_name, model_id, id))
   
       # Drop the old (renamed) column in pivot table, no longer needed
       cursor.execute(
           "ALTER TABLE %s DROP COLUMN %s_id_old" %
           (pivot_table, model_name))
   
   def do_the_final_lifting(apps, schema_editor):
       # Create a new unique index for the old pk column
       index_prefix = '%s_id' % model_table
       new_index_prefix = '%s_email' % model_table
       new_index_name = index_name.replace(index_prefix, new_index_prefix)
   
       cursor.execute(
           "ALTER TABLE %s ADD UNIQUE KEY %s (%s)" %
           (model_table, new_index_name, 'email'))
   
       # Finally, work on the pivot table
       recreate_constraints_and_indices_in_pivot_table()
   

   3. Apply the new migration

Answer 2

This situation is hard to tackle particularly on sqlite which actuall doesn't even have a real ALTER TABLE statement

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table.

Most of the type, django is doing the changes via a temp table. So you can do that too

Step 1: Create a new model, exactly like

class TempModel(models.Model):
    email = models.EmailField(max_length=255)
    # other fields from your existing model

Note that you don't need to explicitly declare a primary key field. Merely switching it off in the email field is sufficient.

Step 2: make migrations and migrate

Step 3: open your favourite database client and do a:

INSERT INTO myapp_tempmodel(fields,....) SELECT * FROM myapp_oldmodel

Step 4: delete old table, make migrations and migrate

Step 5: rename temp table, make migrations and migrate