How to transfer from user primary key to standard id

Question

I created a model with an email address as a user 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 return to the automatically generated id field as the primary key.

How to do it? I tried it differently, but everything failed. I am using Django 1.10.4 with Python 3.4.3 with a SQLite database.

• I just replaced the primary_key = True parameter with unique = True. python manage.py makemigrations complains:

You are trying to add an invalid id field to a user with no default value; we cannot do this (the database needs to fill in the existing lines).

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

2. Based on this message Change the primary key field to a unique field I tried to add Autofield manually, as in:

   id = models.AutoField ()

Now python manage.py makemigrations crash:

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

If I do as suggested by the error message, I get the same problem as in the first attempt: there is no default value.

3. I tried to create the id = IntegerField (unique = True) field (after the Django documentation at https://docs.djangoproject.com/en/1.10/howto/writing-migrations/#migrations-that-add-unique-fields ), and then change the field type in 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 the trace and this error: django.db.utils.OperationalError: duplicate column name: id It seems that you are trying to make an additional column "id", you don’t know why.

What is the right way to do this? Also, if this succeeds, will the ForeignKey fields that refer to my user be changed correctly?

Answer 1

This situation is difficult to solve, especially on sqlite, which does not 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 add a new column to an existing table.

In most cases, django makes changes through a temporary table. So you can do it too

Step 1: Create a New Model, Just Like

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

Note that you do not need to explicitly declare the primary key field. Simply turn it off in the email field.

Step 2: migrate and migrate

Step 4: open your favorite datbase client and do INSERT INTO myapp_tempmodel (fields, ....) SELECT * FROM myapp_oldmodel

Step 4: delete the old table, perform the migration and complete the migration

Step 5: rename the temporary table, perform the migration and complete the migration

Answer 2

I myself ran into this problem and eventually wrote a reusable (albeit for MySQL) migration. Code can be found in this repo . I also wrote about this on the blog .

As a summary, we will take the following steps:

• Change the model class as follows:

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

• Add a new migration to the following 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 new migration