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

Question

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 1

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.

Answer 2

As others have stated, this is entirely the responsibility of the database.

But you should realize that this is the desirable behaviour. An ID uniquely identifies an entity in your database. As such, it should only ever refer to one row. If that row is subsequently deleted, there's no reason why you should want a new row to re-use that ID: if you did that, you'd create a confusion between the now-deleted entity that used to have that ID, and the newly-created one that's reused it. There's no point in doing this and you should not want to do so.

Answer 3

Did you actually drop them from your database or did you delete them using Django? Django won't change AUTO_INCREMENT for your table just by deleting rows from it, so if you want to reset your primary keys, you might have to go into your db and:

ALTER TABLE <my-table> AUTO_INCREMENT = 1;

(This assumes you're using MySQL or similar).