I usual use the RowID as my Serial Number when displaying my records on a Listview. I noticed that whenever a record is deleted a gap is created in the serial numbering. E.g. Lets say I have: 1, 2, 3, 4, 5 and I delete record No 3.
The numbering display becomes 1, 2, 4, 5. Thus leaving a gap. Is there a function one can use to rebuild the rowid numbering to become 1,2,3,4?
I think you can use the vacumn statement. This should rebuild the database and remove the deleted records.
(not to poke, but unless you seldom make changes to the database, you really shouldn't use vacuum much, and if the row-id gap causes you grief, you should probably come up with a different serial number scheme, again, not meaning to poke)
From the SQLite documentation:
QuoteThe normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.
Thank you all for your response.
Wilson, I'd already used VACUUM. No dice.
It seems to me RowID is kinda permanent for every record. But I discovered in SQlite Expert Personal that the RowID is regenerated each time a record is deleted. I wanted something like that.
In the mean time I have improvised another method to generate my serial number (Using INCR statement)
QuoteBut I discovered in SQlite Expert Personal that the RowID is regenerated each time a record is deleted.
SQLite Expert Personal displays a "RecNo" column which is just a generated sequence number. It will be different than the RowID. It does not change the RowID when a record is deleted.
You could change the RowID each time you deleted a record but it would require the updating of each row with a higher RowID than the one you deleted. Could be very expensive.
From SQLite Vacum doc:
QuoteThe VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.
Tried Vacuum this way:
Nothing changed. I guess it acts on the whole DB. Not a specific table.