CODEWITHSUNDEEP
pythondjango
gmarais
gmarais

Reputation: 1891

Django model.delete() reseed primary key

Busy developing my Django app and as it goes I have test data that I would like to delete from some of my tables.

How can I delete the entries and "reset" the primary keys so that the next time an element is inserted, it has id=1?

When I run .delete() I am able to delete the row, but the primary key is not reset.

According to a bit of research this is standard with databases and I need to truncate the table.

Some suggestions are to Truncate the table with raw SQL code:

import sqlite3
conn = sqlite3.connect('db.sqlite3')
cur = conn.cursor()
cur.execute("TRUNCATE TABLE 'table'")

which does not seem to work and just gives the following error:

OperationalError: near "Truncate": syntax error

Other suggestions are to comment out the model and redo migrate. This is not an option either as there are multiple dependent foreign keys across the tables.

Is there no way to reset the primary key of a table?

Upvotes: 1

Views: 433

Answers (2)

IVNSTN
IVNSTN

Reputation: 9324

(too long for comment)

  1. surrogate keys are not supposed to be treated as dense, steady increasing enumerator - it's just a key, a surrogate - not real - key for building a database

  2. dev db, test db, prod db should be separated

  3. beware of demonstrating a surrogate key as it is: invoices/3/ after making an order is clearly saying how popular a company really is.

When you do not test on prod, do not show ids to users, when your code does not rely on assumption that ids started with 1 and each next id=prior id+1, you stop thinking about how pure and beautiful actual id values are. For dev/test purposes - do whatever you want, even (as I said) drop the whole db. If this is a new project and you're preparing db for moving to prod - you might prefer running sql commands directly since this is one time job.

When running tests, Django by default creates and drops a new db.

To make preparation for testing easier there is a fixtures feature.

Upvotes: 2

lorg
lorg

Reputation: 1170

It is unrelated to django, or at least not directly so. You need to reset the value of the sequence that generates the primary key, see https://www.sqlite.org/autoinc.html

Upvotes: 1

Related Questions