We delete some records in a sqlite3 table (re-id record manually) and compact the db. However when adding a new record in firefox sqlite3 manager, the auto incremented id is not the next available id but the id in previous table before compacting. This leaves a huge id gap between the old records and the new records entered. Is there a way we can make the new record taking next available id in firefox sqlie3 manager?
2 Answers
next auto increment id will be the last highest id regardless if it exists or not. You'll have to reset the auto increment id
Here is an easy way to reset the primary key:
Open the table sqlite_sequence in the sqlite3 database. Locate the table you want to reset the primary key and reset column seq to the next available id.