Deleting rows from SQLite database leave gaps.

[andrewhopps]

I've got all functions set to retrieve and display data from my database, but removing a single entry forever ruins any and all further displaying of information. The rows removed leave a gap in the database, such as: 1,2,3,5,6,etc. How do I remove the rows in the database and preserve sequential numbering of rows with no gaps?

Noob friendly responses would be appreciated.

[EatonCode]

Just between 1 noob to another...

If your using keys and you delete from the database.

1, John, Doe
2, Robert, Alexander
3, Man, Bat
4, Simpson, Homer
5, Eaton, David
6, Par, Robert

And you do something like this...

delete from mydata where key=4

Your going to be left with.

1, John, Doe
2, Robert, Alexander
3, Man, Bat
5, Eaton, David
6, Par, Robert

Your Next Index will still be 7

You could reindex your key values.. but if you reference anything from the keys, it will not have the correct information anymore.

Look at the answer here.

http://stackoverflow.com/questions/13644577/how-to-re-index-number-sorted-sqlite-table

Hope this helps.

[andrewhopps]

@EatonCode

just leaving the answer as found, I'm gathering that you're setting the rowid to their current index in the database with this?

 UPDATE todo SET id = rowid

[EatonCode]

@andrewhopps I was referring to this... http://stackoverflow.com/a/13644816

Not really sure what your going for but, from my own experience in my past database design I made this same mistake....

Now with some experience under my belt I would never re-index unless I had thousands of missing numbers and I never referenced the key some else in the database.

using the example above..

Select key from mydata where key = 6 Expecting Par, Robert.

If you re-indexed key 6 may not exist. Or worse a wrong name......

Hope it works out. :)

[andrewhopps]

@EatonCode
My database, more importantly table is of only 2 columns and displayed in a QTableView and is updated after any change to the table. So the table is always displaying the current tables information. I edit the database by selecting the row in the QTableView and making my changes in text fields and pressing an update button. All fetching from the database is relative so their starting indexes/rowids do not matter. Constantly remaking a table over and over again seems insanely overdone, so forgive my lack of understanding and or clarifying before, but I just need to get things in sequential order in the simplest way possible regardless of how many rows I add or remove. I can add another query to reset it after each edit, I just don't know what I need to write to accomplish that.

[Tom_H]

@andrewhopps Doing a vacuum will rewrite the ROWIDs, but you don't want to do that. Keep a map of rowid to table row number.

[andrewhopps]

@Tom_H After messing around and looking further into it, this does 100% what I was looking for. So thank you for the suggestion, even if it wasn't exactly the intent.