What is the best approach to change primary keys in an existing Django app?

0 votes
asked Jan 13, 2010 by luc

I have an application which is in BETA mode. The model of this app has some classes with an explicit primary_key. As a consequence Django use the fields and doesn't create an id automatically.

class Something(models.Model):
    name = models.CharField(max_length=64, primary_key=True)

I think that it was a bad idea (see unicode error when saving an object in django admin) and I would like to move back and have an id for every class of my model.

class Something(models.Model):
    name = models.CharField(max_length=64, db_index=True)

I've made the changes to my model (replace every primary_key=True by db_index=True) and I want to migrate the database with south.

Unfortunately, the migration fails with the following message: ValueError: You cannot add a null=False column without a default value.

I am evaluating the different workarounds for this problem. Any suggestions?

Thanks for your help

5 Answers

0 votes
answered Jan 13, 2010 by tobu

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)
0 votes
answered Jan 13, 2010 by s-lott

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.

  1. 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.

  2. 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.

  3. Create the new schema. This will only create the tables which are changing.

  4. 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.

  1. Rename all your old tables.

  2. Create the entire new schema.

  3. Write SQL to migrate all the data from old schema to new schema. This will have to cleverly reassign keys as it goes.

  4. Drop the renamed old tables.

 

0 votes
answered Jan 3, 2012 by remcogerlich

I had the same problem to day and came to a solution inspired by the answers above.

My model has a "Location" table. It has a CharField called "unique_id" and I foolishly made it a primary key, last year. Of course they didn't turn out to be as unique as expected at the time. There is also a "ScheduledMeasurement" model that has a foreign key to "Location".

Now I want to correct that mistake and give Location an ordinary auto-incrementing primary key.

Steps taken:

  1. Create a CharField ScheduledMeasurement.temp_location_unique_id and a model TempLocation, and migrations to create them. TempLocation has the structure I want Location to have.

  2. Create a data migration that sets all the temp_location_unique_id's using the foreign key, and that copies over all the data from Location to TempLocation

  3. Remove the foreign key and the Location table with a migration

  4. Re-create the Location model the way I want it to be, re-create the foreign key with null=True. Renamed 'unique_id' to 'location_code'...

  5. Create a data migration that fills in the data in Location using TempLocation, and fills in the foreign keys in ScheduledMeasurement using temp_location

  6. Remove temp_location, TempLocation and null=True in the foreign key

And edit all the code that assumed unique_id was unique (all the objects.get(unique_id=...) stuff), and that used unique_id otherwise...

0 votes
answered Jan 20, 2012 by user920391

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.

0 votes
answered Sep 15, 2017 by salvalcantara

I have come across this problem myself and ended up writing a reusable (MySQL-specific) migration which also takes into account a Many-To-Many relationship. As a summary, the steps I took were:

  1. Modify the model class like this:

    class Something(models.Model):
        name = models.CharField(max_length=64, 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)
    
    
    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='name',
                field=models.CharField(max_length=64, 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.name] = 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(30)'))
        # ... 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_name' % 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, 'name'))
    
        # Finally, work on the pivot table
        recreate_constraints_and_indices_in_pivot_table()
    
    1. Apply the new migration

You can find the complete code in this repo. I've also written about it in my blog.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...