• Welcome, Guest. Please login.
 
August 14, 2020, 08:24:07 pm

News:

Welcome to the SQLitening support forums!


Rebuilding RowID After Deleting

Started by Fredrick Ughimi, August 02, 2011, 03:39:14 pm

Previous topic - Next topic

Fredrick Ughimi

Hello,

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?

Best regards,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

D. Wilson

I think you can use the vacumn statement. This should rebuild the database and remove the deleted records.

Jim Dunn

(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)
3.14159265358979323846264338327950
"Ok, yes... I like pie... hate roman numerals."

Rolf Brandt

From the SQLite documentation:

http://www.sqlite.org/autoinc.html
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.


I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Fredrick Ughimi

Hello,

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)

Thank you.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

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.



Fredrick Ughimi

August 04, 2011, 08:13:43 pm #6 Last Edit: August 05, 2011, 04:35:34 am by Fredrick Ughimi
Tried Vacuum this way:



slExe "Vacuum"



Nothing changed. I guess it acts on the whole DB. Not a specific table.

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet