How to migrate from custom primary key to default id
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:
Modify your model class like this:
class Something(models.Model): email = models.EmailField(max_length=255, unique=True)
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()
- Apply the new migration
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