SQLite Drop and Recreate Table

Question

How do I drop and recreate a table with updated information?

For example; I have a table in my application with 5 records.

ID     Entry
1      a
2      b
3      c
4      d
5      e

Now when I delete a record lets say, ID 3 the table becomes

 ID     Entry
1      a
2      b
4      d
5      e

How can I drop it, and recreate it to become this:

  ID     Entry
1      a
2      b
3      d
4      e

Actually the deletion is controlled by the user so any record can be deleted at any time.

Answer 1

You could do following: delete the row You want to delete:

     public boolean deleteRow(int id) 
      {
         return db.delete(YOUR_TABLE, ROW_ID + "=" + _id, null) > 0;
       }

read remaining entries (only entries, not the id) into an ArrayList, drop Your table:

        db.execSQL("DROP TABLE IF EXISTS " + YOUR_TABLE);

and recreate Your table. Then the ID Numbers will be again from 1-XX in Order

Answer 2

I know this is an old post but I thought I'd clear things up for others who may be doing the same thing as you based on your comment:

When a record is deleted the deletion process is done by the currently selected row since I can only select up to row 4, ID 5 can never be deleted.

You really shouldn't recreate your ids. It messes things up if they are used as keys in another table, plus if you have a large database, recreating them can just waste time when there is really no need to.

What you should do is save an array of the ids along with an array of the entries so you know which id to delete based on which row they selected (or some other way of linking the ids and values so you can grab them later).

In the above example, after someone deletes the row with id 3, your arrays would look like this:

entries = { "a","b","d","e" }
ids = { 1,2,4,5 }

if someone selects "e", which is the fourth item, you simply delete from the table where the id equals the fourth value in the ids array, 5 in this case.

This way if items are constantly being added and removed, your database could end up with ids 2, 34, 56, and 435. It doesn't matter because your code will keep track and know which id to delete.