How To Restore A Continuous Sequence Of Ids As Primary Keys In A Sql Database?

I'm using a SQlite database and Django's QuerySet API to access this database. I wrote data sequentially into the database and each entry has a simple ID as the primary key (this i

Solution 1:

I cannot think of any situation in which doing this would be desirable. The best primary keys are immutable (although that's not a technical requirement) and the very purpose of using non-meaningful integer primary keys is to avoid having to update them.

I would even go so far as to say that if you require a meaningful, unbroken sequence of integers, create a separate column in your table, keep the primary key with its sequence breaks, and renumber the new "sequence" column when needed.

However, you may have requirements that I can't think of. If you reallyneed to change the values in those keys make sure that all the references to that column in your database are protected by FOREIGN KEY constraints and check out the ON UPDATE CASCADE option when you declare a foreign key. It will instruct the database to do the updating for you.

But if you don't have to this, don't.