SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Fredrick Ughimi on August 02, 2011, 03:39:14 pm

Title: Rebuilding RowID After Deleting
Post by: Fredrick Ughimi on August 02, 2011, 03:39:14 pm
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,
Title: Re: Rebuilding RowID After Deleting
Post by: D. Wilson on August 02, 2011, 10:05:40 pm
I think you can use the vacumn statement. This should rebuild the database and remove the deleted records.
Title: Re: Rebuilding RowID After Deleting
Post by: Jim Dunn on August 02, 2011, 10:40:17 pm
(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)
Title: Re: Rebuilding RowID After Deleting
Post by: Rolf Brandt on August 03, 2011, 03:20:52 am
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.


Title: Re: Rebuilding RowID After Deleting
Post by: Fredrick Ughimi on August 03, 2011, 05:24:10 am
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.
Title: Re: Rebuilding RowID After Deleting
Post by: Fred Meier on August 03, 2011, 11:33:02 am
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.


Title: Re: Rebuilding RowID After Deleting
Post by: Fredrick Ughimi on August 04, 2011, 08:13:43 pm
Tried Vacuum this way:



slExe "Vacuum"



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