How can I resequence a primary index in sqlite for use with a UITableView?

Question

I'm new to sqlite so please be patient :)

I've got a UITableView reading the data from a sqlite db. If i delete a record, the row in the db is deleted. The problem is that the other rows keep their primary keys, so for example, I have 3 rows and the primary keys are 1,2,4. is there any possibility to update the keys to be 1,2,3?

Thanks a lot!

edit: I create my table with:

CREATE TABLE mytable (ID INTEGER PRIMARY KEY  AUTOINCREMENT, value1 TEXT);

Answer 1

Use REINDEX. (REINDEX only rebuilds the index structure, it doesn't actually change the values of any indexes.)

Use the ordering of the rows as an implicit index

Rather than trying to change the index values of a whole bunch of rows, why don't you perform a mapping of the sorted order?

Use a SELECT statement like:

NSString* query = @"SELECT * FROM myTable ORDER BY id LIMIT 1 OFFSET %d";
query = [NSString stringWithFormat:query, indexPath.row + 1];

To DELETE a row do something like:

NSString* query = @"DELETE FROM myTable WHERE ID = (SELECT ID FROM myTable ORDER BY ID LIMIT 1 OFFSET %d)";
query = [NSString stringWithFormat:query, indexPath.row + 1];

To INSERT, perform a regular insertion like usual.

This way, when you delete one row, you don't end up needing to edit every row that has an ID larger than the row you're deleting. This will be much faster.

Make sure to update your table view whenever a change in the DB occurs to keep the table view consistent with the DB.

This also has the nice side-effect of keeping your table view sorted.

Define a trigger to change affected indexes

If you really want to keep indexes so that they're a perfect series (i.e.: 1,2,3,4,...)

You can define a delete trigger like so:

CREATE TRIGGER reindex AFTER DELETE ON myTable FOR EACH ROW
BEGIN
    UPDATE myTable SET ID = old.ID - 1 WHERE ID > old.ID;
END;