34

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.

pj2452
  • 905
  • 3
  • 10
  • 22

7 Answers7

32

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.

knbk
  • 52,111
  • 9
  • 124
  • 122
  • Thanks. I was just curious why I saw different functionality between my online/offline versions. – pj2452 Jan 01 '15 at 23:34
  • @pj2452 What database engine are you using offline? – knbk Jan 01 '15 at 23:35
  • @pj2452 Nvm that, it's probably because the database server in your offline environment is often restarted. I updated my answer to reflect that. – knbk Jan 01 '15 at 23:39
  • Thank you. That makes a lot more sense now that I think of it. – pj2452 Jan 02 '15 at 00:24
27

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.

Daniel Roseman
  • 588,541
  • 66
  • 880
  • 895
  • What I don't understand is why the offline version is not acting in the same manner as the online version. – pj2452 Jan 01 '15 at 23:31
  • using unit tests that need to spawn certain test data in the database would be a case, unless you want to generate a new table with every test. – Mehdi Jul 14 '16 at 11:36
  • Are you using the same database in development as in production? In particular sqlite frees the keys of deleted objects for reuse (unfortunately, if you depend on the uniqueness of the key). https://sqlite.org/autoinc.html – DylanYoung Sep 30 '17 at 19:20
6

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

xnx
  • 24,509
  • 11
  • 70
  • 109
  • Thanks. I deleted the instances thru the Django admin page. Can this not be done through the Django API? – pj2452 Jan 01 '15 at 23:33
  • Not as far as I know -- but see @knbk's answer for dropping and recreating the table using `manage.py`. Unless you really need the primary keys to take specific values, it's better not to worry about them not starting at 1. – xnx Jan 01 '15 at 23:53
  • As noted above, the pk generation policy has little to do with Django, unless that DB has no native auto-increment support. The database backend will determine how the keys are recycled. – DylanYoung Sep 30 '17 at 19:25
3

There is no issue, that's the way databases work. Django doesn't have anything to do with generating ids it just tells the database to insert a row and gets the id in response from database. The id starts at 1 for each table and increments every time you insert a row. Deleting rows doesn't cause the id to go back. You shouldn't usually be concerned with that, all you need to know is that each row has a unique id.
You can of course change the counter that generates the id for your table with a database command and that depends on the specific database system you're using.

nima
  • 6,566
  • 4
  • 45
  • 57
  • As commented above, I understand that this is default behavior but I don't understand why the offline/online differ. – pj2452 Jan 01 '15 at 23:33
  • Your local database will act exactly the same, and wont reassign ids. You may have recreated your entire database. – nima Jan 02 '15 at 07:58
  • sqlite takes the highest existing id + 1 so this answer is not entirely correct. Deleting the highest-id row will cause that same id to be used for the next created row. – DylanYoung Sep 30 '17 at 19:24
3

If you are using SQLite you can reset the primary key with the following shell commands:

DELETE FROM your_table; DELETE FROM SQLite_sequence WHERE name='your_table';

Juan
  • 477
  • 4
  • 8
2

Another solution for 'POSTGRES' DBs is from the UI. Select your table and look for 'sequences' dropdown and select the settings and adjust the sequences that way.

example:

picture from pgadmin

Unheilig
  • 16,196
  • 193
  • 68
  • 98
Toasty89
  • 21
  • 1
0

I'm not sure when this was added, but the following management command will delete all data from all tables and will reset the auto increment counters to 1.

./manage.py sqlflush | psql DATABASE_NAME
Dan Loewenherz
  • 10,879
  • 7
  • 50
  • 81
  • 1
    Almost: it deletes all data from all tables *except* the migration table, which is preserved for obvious reasons (this command exists specifically to preserve the migration table. If you don't need that preserved then you don't need to flush, you just need a dropdb + createdb, followed by `manage migrate`) – Mike 'Pomax' Kamermans Aug 22 '17 at 15:54