Update ID field in SQLite database when an entry is removed

Question

I have a SQLite database in my android application which holds multiple username/password entries, this content is placed into a ListView.

What I would like to achieve, is when an item is longpressed, it is removed from the list, but I'm encountering an issue.

I use the following code to remove an entry from the database:

    public void deleteEntry(int ID) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_LOGINS, KEY_ID + " = ?",
            new String[]{Integer.toString(ID)});
    db.close();
    }

The data is stored in the database in the following manner:

--------------------------
1|server|username|password
2|server|username|password
3|server|username|password
4|server|username|password
--------------------------

Now if I was to mark entry #2 for deletion, I would end up with the following

--------------------------
1|server|username|password
3|server|username|password
4|server|username|password
--------------------------

Which causes issues attempting to access the other entries, as I do all my lookups using the first field. Using the position from my OnItemLongClickListener, if I was to press the 2nd item in the list, it would be searching for an item with ID of 2.

How can I update the entries in my database so that my ID field will be sequentially numbered? Is there a better alternative to using an ID field?

How is the data being displayed in the list? I mean are you sorting it in any way? — TeChNo_DeViL, Jan 28 '16 at 19:55
@TeChNo_DeViL I display it in the order I receive it from the database — keag, Jan 28 '16 at 20:21
@CL because there is no 0 id field, the database starts with ID 1 — keag, Jan 28 '16 at 20:21

score 0 · Answer 1 · edited May 23 '17 at 12:04

It would be best to simply reset your identity columns, you can refer to this question: SQL Server Reset Identity Increment for all tables

Note that relying on all your columns being incremental is fairly bad practice, I would advise you link each row in the database to a row in your program so when loading, you select each item from the database and create a row for it in your program, this eliminates this issue.

score 0 · Answer 2 · answered Jan 28 '16 at 20:33

I can suggest a simple change.

When you load the list (using the CursorLoader if you are using so), maintain a list of ids that are loaded. This can be achieved by declaring a global ArrayList<Integer> holding the ids of the loaded fields. Now using the onLongItemClicked or likewise methods, you can simply delete the rows from the database using the IDs by using get(position) method on the ArrayList<Integer> object to get the ID currently held by a row item. Finally after deleting from the database, delete the ID from the ArrayList as well, and you're ready for another round of deletion.

Update ID field in SQLite database when an entry is removed

2 Answers2