Django model instances primary keys do not reset to 1 after all instances are deleted

django primary-key models instances
pj2452 picture pj2452 · Jan 1, 2015 · Viewed 25.5k times · Source

I have been working on an offline version of my Django web app and have frequently deleted model instances for a certain ModelX.

I have done this from the admin page and have experienced no issues. The model only has two fields: name and order and no other relationships to other models.

New instances are given the next available pk which makes sense, and when I have deleted all instances, adding a new instance yields a pk=1, which I expect.

Moving the code online to my actual database I noticed that this is not the case. I needed to change the model instances so I deleted them all but to my surprise the primary keys kept on incrementing without resetting back to 1.

Going into the database using the Django API I have checked and the old instances are gone, but even adding new instances yield a primary key that picks up where the last deleted instance left off, instead of 1.

Wondering if anyone knows what might be the issue here.

Answer

knbk picture knbk · Jan 1, 2015

I wouldn't call it an issue. This is default behaviour for many database systems. Basically, the auto-increment counter for a table is persistent, and deleting entries does not affect the counter. The actual value of the primary key does not affect performance or anything, it only has aesthetic value (if you ever reach the 2 billion limit you'll most likely have other problems to worry about).

If you really want to reset the counter, you can drop and recreate the table:

python manage.py sqlclear <app_name> > python manage.py dbshell

Or, if you need to keep the data from other tables in the app, you can manually reset the counter:

python manage.py dbshell
mysql> ALTER TABLE <table_name> AUTO_INCREMENT = 1;

The most probable reason you see different behaviour in your offline and online apps, is that the auto-increment value is only stored in memory, not on disk. It is recalculated as MAX(<column>) + 1 each time the database server is restarted. If the table is empty, it will be completely reset on a restart. This is probably very often for your offline environment, and close to none for your online environment.

Related questions

Primary key and unique key in django

I had a custom primary key that need to be set up on a particular data in a model. This was not enough, as an attempt to insert a duplicate number succeeded. So now when i replace primary_key=True …

Read More
Django queries - id vs pk

When writing django queries one can use both id/pk as query parameters. Object.objects.get(id=1) Object.objects.get(pk=1) I know that pk stands for Primary Key and is just a shortcut, according to django's documentation. However it …

Read More
How can set two primary key fields for my models in Django

I have a model like this: class Hop(models.Model): migration = models.ForeignKey('Migration') host = models.ForeignKey(User, related_name='host_set') I want to migration and host both together be the primary key.

Read More