Django AUTO INCREMENT for PK

Question

I have some models in my Django app and I saw the field 'auto_increment_id' (as PK) doesn't work as 'AUTO INCREMENT' (the DB is Postgres).

I made 2 records with auto_increment_id 1 and 2, delete record 2 and make a new one with auto_increment_id=3 instead of auto_increment_id=2.

models.py

class Testme(models.Model):
    auto_increment_id = models.AutoField(primary_key=True)
    year = models.ForeignKey(
        Year,
        verbose_name="Year",
        blank=False,
        on_delete=models.CASCADE,
    )

    def __str__(self):
        return str(self.auto_increment_id)

How can I fix it?

Thanks a lot!

Answer 1

There's nothing to fix. Postgres uses a counter for this - it doesn't take "the first free space" in the id range, it just uses the next value of the counter.

It's only there to be a unique value, so having gaps in the range where you've deleted records is not an issue.

(Also, no need to add this yourself - if you don't specify primary_key=True on any field, Django will automatically add an id field that does this).

Answer 2

'AUTO INCREMENT' is maintained by DB(in your case, Postgres), so to reset your auto increment value you need to alter your sequence in database. Check this link to know how to reset manually Reset auto increment counter in postgres

I don't know about your detailed scenario, but altering auto increment could be dangerous. For example, user creates 1, 2, 3 and deletes 2, then what is proper value to restart 2 or 4?

Nevertheless if you decide to maintain auto increment value manually, you can consider using Django Signal(post_delete). Using signal, you can altering database sequence after post deleted.