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

Question

Hello,

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 http://stackoverflow.com/questions/2011629/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

Answer 1

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)

Answer 2

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 user's swear on their mother's 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.