Django: Best practice regarding auto increment primary key

Question

Hi I am building a Django application and during this I am doing test uploads into my database, then deleting the data. I understand that when I do this, the next insert will 'pick up where it left off' when inserting the auto-increment primary key int.

I.e:

I insert 3 rows into my table 'Sample':

auto_id    |     sample 
  1        |    JSUDH172
  2        |    QIUWJ185
  3        |    PNMSY111

When i delete these, and enter them in again, it will start at 4:

auto_id    |     sample 
  4        |    JSUDH172
  5        |    QIUWJ185
  6        |    PNMSY111

I understand this is built in to stop Django overwriting primary keys, but when the primary keys are no longer there, it (mainly from a superficial point of view) annoys me that it doesn't reset. I am uploading thousands of rows into certain tables during this development, so sometimes it starts hundreds/thousands in.

I know I can always restart the project 'from scratch' after development is finished, but I was wondering whether anyone had any useful workarounds or if people just left it as it is (if they have the same view as me)

thanks

The superficial annoyance is something to live with. The alternatives of reseeding and then updating all references in child tables is far more pain than arbitrary IDs. You could have your test's run against another database if you are concerned over ID space — Andrew, Mar 20 '18 at 13:45
What kind of RDBMS are you using? Postgres? SQL Server? You could generate your migration, and then go modify it by adding the sql command to reseed your identity column. Or potentially use fixtures? — dfundako, Mar 20 '18 at 13:47

Dap · Answer 1 · 2018-03-20T14:22:20.533

So there a few methods. Some of which have been mentioned already. First, it is important to know. While auto_increment is set on the django model field, it translates into a database command. When you run

python3 manage.py migrate

for a model that has an auto_increment field, it executes in a sql command like this

When you delete your data, go into your sql cli and reset it manually. However, interacting with the database directly in a non-programatic manner, is not the django way and shouldn't be done. In the case, you HAVE TO do delete all the data and reset auto increment, lets say on production, I would create a migration script via ./manage.py makemigrations theappname --empty. and execute the sql statement within the migration script.

score 0 · Answer 2 · answered Mar 20 '18 at 16:46

Usually the value of an integer primary key is based on an underlying sequence in the database [1][2], which allows unique values to be allocated in a way that is safe under all the multi-transaction, multi-tenant, multi-connection racey glory that is a client-server database. Safely filling in the gaps in a sparsely-filled sequence of IDs is much more complex than "get and increment", and doesn't have any practical value for a column where the constraint is functional rather than aesthetic.

From a practical standpoint, you will not run out of integer primary keys in almost all cases so there's no actual need to reuse vacant slots.

If you need a sequence number that is smoothly filled from zero, maybe you can have another column for that purpose and keep it filled by another mechanism, maybe using a stored procedure to find the smallest available value to use with a new row.

[1] https://www.postgresql.org/docs/10/static/sql-createsequence.html

[2] https://www.postgresql.org/docs/10/static/functions-sequence.html

Django: Best practice regarding auto increment primary key

2 Answers2