Django: how to correctly delete database record to prevent primary key reuse?

Question

My expectation is that even after I delete a record with primary key pk=k, and later create a new record, this new record will get at least pk=k+1.

Environment: Python 2.7.8 + Django 1.6.5 + default sqlite3 enginie

My /repeatId/models.py:

from django.db import models

# Create your models here.
class Foo(models.Model):
    name = models.CharField(max_length=30)
    def __unicode__(self):
        return self.name

manage.py shell interaction:

>>> from repeatId.models import Foo
>>> Foo.objects.all().count()
0
>>> Foo.objects.create(name='first')
<Foo: first>
>>> Foo.objects.all().count()
1
>>> foo = Foo.objects.all()[0]
>>> foo.id
1
>>> Foo.objects.get(id=1).delete()          <--- deleting initial obj with id=1
>>> Foo.objects.all().count()
0
>>> Foo.objects.create(name='second')
<Foo: second>
>>> Foo.objects.all().count()
1
>>> foo = Foo.objects.all()[0]
>>> foo.id                                  <--- expected to get id=2, but got id=1
1

Here's the Postgres side answer. Don't know about Django. Seems like you have to get pretty in depth to solve this problem. http://stackoverflow.com/questions/15267050/postgresql-properly-change-id-of-table-row — Garrett Kadillak, Sep 28 '14 at 04:03
Did you create the database using the version of Django you're currently running, or is it an old database? — Joachim Isaksson, Sep 28 '14 at 06:56
@JoachimIsaksson Example in the post is produced in a newly created project and newly created app. The database of the project I'm working on was created under Django 1.5. So I think both case would fail. — wlnirvana, Sep 28 '14 at 07:01
@wlnirvana I can't quite figure out in [which version of Django the problem was fixed](https://github.com/django/django/commit/eade315da1c8372ac1dfcf1fd20ea87f454d71ac) but it looks like 1.7. It also mentions that the database needs to be migrated to fix the problem, since the code fix only affects new tables. — Joachim Isaksson, Sep 28 '14 at 07:04

score 2 · Accepted Answer · answered Sep 28 '14 at 07:56

2

This is a bug in Django that has been fixed in version 1.7.

answered Sep 28 '14 at 07:56

CL.

173,858
17
217
259

score 0 · Answer 2 · answered Sep 28 '14 at 07:25

Create another model that forces only one record to store the last used pk for your foo model:

class FooPK(models.Model):
    last_used_foo_pk = models.IntegerField()
    def save(self, *args, **kwargs):
        if self.pk == 1: #force only ONE record to ever be in the db
            super(FooPK, self).save(*args, **kwargs)

Override Foo's save method to get the last used pk from FooPK, increment it, save it to both models

class Foo(models.Model):
    ...
    def save(self, *args, **kwargs):
        PKRecord = FooPK.objects.get(pk=1) #FooPK's only object pk will not change, you have forced only the first record to ever 
        new_pk = PKRecord.last_used_foo_pk + 1
        self.pk = new_pk
        super(Foo, self).save(*args, **kwargs)
        PKRecord.last_used_foo_pk = new_pk
        PKRecord.save()

Django: how to correctly delete database record to prevent primary key reuse?

2 Answers2