What is the best approach to change primary keys in an existing Django app?
Agreed, your model is probably wrong.
The formal primary key should always be a surrogate key. Never anything else. [Strong words. Been database designer since the 1980's. Important lessoned learned is this: everything is changeable, even when the users swear on their mothers' graves that the value cannot be changed is is truly a natural key that can be taken as primary. It isn't primary. Only surrogates can be primary.]
You're doing open-heart surgery. Don't mess with schema migration. You're replacing the schema.
Unload your data into JSON files. Use Django's own internal django-admin.py tools for this. You should create one unload file for each that will be changing and each table that depends on a key which is being created. Separate files make this slightly easier to do.
Drop the tables which you are going to change from the old schema.
Tables which depend on these tables will have their FK's changed; you can either update the rows in place or -- it might be simpler -- to delete and reinsert these rows, also.
Create the new schema. This will only create the tables which are changing.
Write scripts to read and reload the data with the new keys. These are short and very similar. Each script will use
json.load()to read objects from the source file; you will then create your schema objects from the JSON tuple-line objects that were built for you. You can then insert them into the database.You have two cases.
Tables with PK's change changed will be inserted and will get new PK's. These must be "cascaded" to other tables to assure that the other table's FK's get changed also.
Tables with FK's that change will have to locate the row in the foreign table and update their FK reference.
Alternative.
Rename all your old tables.
Create the entire new schema.
Write SQL to migrate all the data from old schema to new schema. This will have to cleverly reassign keys as it goes.
Drop the renamed old tables.
Currently you are failing because you are adding a pk column that breaks the NOT NULL and UNIQUE requirements.
You should split the migration into several steps, separating schema migrations and data migrations:
- add the new column, indexed but not primary key, with a default value (ddl migration)
- migrate the data: fill the new column with the correct value (data migration)
- mark the new column primary key, and remove the former pk column if it has become unnecessary (ddl migration)
To change primary key with south you can use south.db.create_primary_key command in datamigration. To change your custom CharField pk to standard AutoField you should do:
1) create new field in your model
class MyModel(Model):
id = models.AutoField(null=True)
1.1) if you have a foreign key in some other model to this model, create new fake fk field on these model too (use IntegerField, it will then be converted)
class MyRelatedModel(Model):
fake_fk = models.IntegerField(null=True)
2) create automatic south migration and migrate:
./manage.py schemamigration --auto
./manage.py migrate
3) create new datamigration
./manage.py datamigration <your_appname> fill_id
in tis datamigration fill these new id and fk fields with numbers (just enumerate them)
for n, obj in enumerate(orm.MyModel.objects.all()):
obj.id = n
# update objects with foreign keys
obj.myrelatedmodel_set.all().update(fake_fk = n)
obj.save()
db.delete_primary_key('my_app_mymodel')
db.create_primary_key('my_app_mymodel', ['id'])
4) in your models set primary_key=True on your new pk field
id = models.AutoField(primary_key=True)
5) delete old primary key field (if it is not needed) create auto migration and migrate.
5.1) if you have foreign keys - delete old foreign key fields too (migrate)
6) Last step - restore fireign key relations. Create real fk field again, and delete your fake_fk field, create auto migration BUT DO NOT MIGRATE(!) - you need to modify created auto migration: instead of creating new fk and deleting fake_fk - rename column fake_fk
# in your models
class MyRelatedModel(Model):
# delete fake_fk
# fake_fk = models.InegerField(null=True)
# create real fk
mymodel = models.FoeignKey('MyModel', null=True)
# in migration
def forwards(self, orm):
# left this without change - create fk field
db.add_column('my_app_myrelatedmodel', 'mymodel',
self.gf('django.db.models.fields.related.ForeignKey')(default=1, related_name='lots', to=orm['my_app.MyModel']),keep_default=False)
# remove fk column and rename fake_fk
db.delete_column('my_app_myrelatedmodel', 'mymodel_id')
db.rename_column('my_app_myrelatedmodel', 'fake_fk', 'mymodel_id')
so previously filled fake_fk becomes a column, that contain actual relation data, and it does not get lost after all the steps above.
I managed to do this with django 1.10.4 migrations and mysql 5.5, but it wasn't easy.
I had a varchar primary key with several foreign keys. I added an id field, migrated data and foreign keys. This is how:
- Adding future primary key field. I added an
id = models.IntegerField(default=0)field to my main model and generated an auto migration. Simple data migration to generate new primary keys:
def fill_ids(apps, schema_editor): Model = apps.get_model('<module>', '<model>') for id, code in enumerate(Model.objects.all()): code.id = id + 1 code.save() class Migration(migrations.Migration): dependencies = […] operations = [migrations.RunPython(fill_ids)]Migrating existing foreign keys. I wrote a combined migration:
def change_model_fks(apps, schema_editor): Model = apps.get_model('<module>', '<model>') # Our model we want to change primary key for FkModel = apps.get_model('<module>', '<fk_model>') # Other model that references first one via foreign key mapping = {} for model in Model.objects.all(): mapping[model.old_pk_field] = model.id # map old primary keys to new for fk_model in FkModel.objects.all(): if fk_model.model_id: fk_model.model_id = mapping[fk_model.model_id] # change the reference fk_model.save() class Migration(migrations.Migration): dependencies = […] operations = [ # drop foreign key constraint migrations.AlterField( model_name='<FkModel>', name='model', field=models.ForeignKey('<Model>', blank=True, null=True, db_constraint=False) ), # change references migrations.RunPython(change_model_fks), # change field from varchar to integer, drop index migrations.AlterField( model_name='<FkModel>', name='model', field=models.IntegerField('<Model>', blank=True, null=True) ), ]Swapping primary keys and restoring foreign keys. Again, a custom migration. I auto-generated the base for this migration when I a) removed
primary_key=Truefrom the old primary key and b) removedidfieldclass Migration(migrations.Migration): dependencies = […] operations = [ # Drop old primary key migrations.AlterField( model_name='<Model>', name='<old_pk_field>', field=models.CharField(max_length=100), ), # Create new primary key migrations.RunSQL( ['ALTER TABLE <table> CHANGE id id INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT'], ['ALTER TABLE <table> CHANGE id id INT (11) NULL', 'ALTER TABLE <table> DROP PRIMARY KEY'], state_operations=[migrations.AlterField( model_name='<Model>', name='id', field=models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID'), )] ), # Recreate foreign key constraints migrations.AlterField( model_name='<FkModel>', name='model', field=models.ForeignKey(blank=True, null=True, to='<module>.<Model>'), ]