How to revert Django PostgreSQL database model's primary key to an AutoField whilst maintaining foreign key and many to many relationships

manpreet Best Answer 2 years ago

I currently have a Django powered in-production web app that contains multiple models, sitting on top of a Postgresql database (Google Cloud SQL)

During initial set-up, one of the models was set up as follows:

class ExampleModel(models.Model):
    id = models.CharField(max_length=60, unique=True, primary_key=True)
    new_id = models.CharField(max_length=60, unique=True, null=True, db_index=True)
    name = models.CharField(max_length=300, db_index=True)
    tags = models.ManyToManyField(Tag, blank=True)

The id field contains a unique ID like: AB123456789.

I have since realised this is a mistake and would like to revert the primary key field to a standard auto-incrementing autofield, and instead use the 'new_id' field to store the unique ID.

Please can someone provide guidance on how I can make this change and perform the necessary database migrations? There are a number of foreign key fields in other models that currently use the id field in the above model which will need changing. As you can see in the above, there is also a many to many field between this model and a tag model.

I tried removing the id field from my models.py file and migrating - it initially gave an error linked to null fields and default values so I set a dummy default value in the Terminal window and removed this in the migration file.

The database removed the id field successfully and generated a new Autonumber primary key field however none of the many to many or foreign key relationships were kept post migration. I have since rolled back to a prior version of the database.

manpreet 2 years ago

Generally this will be your approach. Steps 1-4 can be merged into a single deployment. Steps 5-7 into another. Then 8-9 would be the final.

1. Create new auto field.
2. Create new FK nullable relationships on models
3. Update all code that creates related models to populate both FK's
4. Populate all the null FK fields via a script
5. Make the new FK fields not-nullable
6. Make old FK's nullable.
7. Remove old FK usages from code base
8. Migration to remove old ID field and FKs.
9. (optional) Rename auto field to be ID and potentially use Django's built-in field.