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.

Why do you want to achieve this? You would need to recreate the database everytime some user change something in it. — Leandros, Jan 22 '13 at 08:43
why do you need this ? rather than finding a cumbersome way of answering to your question, i'd rather find a way to not need it. — njzk2, Jan 22 '13 at 08:45
no. I want to recreate the table not the database. There's a few reasons why; for example: 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. — Pztar, Jan 22 '13 at 08:46
possible duplicate of [Android SQLite Database, WHY drop table and recreate on upgrade](http://stackoverflow.com/questions/19793004/android-sqlite-database-why-drop-table-and-recreate-on-upgrade) — Esqarrouth, Dec 29 '14 at 22:59

score 13 · Answer 1 · edited Feb 03 '14 at 15:54

13

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

edited Feb 03 '14 at 15:54

Arif Amirani

26,265
3
33
30

answered Jan 22 '13 at 09:15

Opiatefuchs

9,800
2
36
49

1

that's pretty much what I have. However when I drop the table and then go to recreate it, it comes back as a NullPointer. I don't know I guess I'll have to fiddle around with it some more. – Pztar Jan 22 '13 at 09:19
so could You please show the code where You have done this? For example if You have it done in a wrong order, it is possible that You delete Your table before saving it to the arrayList. That could be the reason why You get a nullpointer. In germany we say, sometimes you couldn´t see the forest in cause of so many trees :) – Opiatefuchs Jan 22 '13 at 09:29
Need to add a space after EXISTS – Matthew Jan 23 '14 at 17:56
1

doesn't it need to have a semicolon at the end of the query? db.execSQL("DROP TABLE IF EXISTS " + YOUR_TABLE +";"); – Lendl Leyba Sep 30 '14 at 03:39

score 1 · Answer 2 · answered Feb 17 '16 at 16:20

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.

score 0 · Answer 3 · answered Mar 08 '18 at 20:41

An other approach

public boolean deleteTable(int id) {
    SQLiteDatabase database = dbHelper.getReadableDatabase();
    final String whereClause = DBHelper.COLUMN_ID + "=?";
    final String whereArgs[] = {String.valueOf(id)};
    int affectedRows = database.delete(DBHelper.TABLE_NAME_OGR, whereClause, whereArgs);
    return affectedRows > 0;
}

SQLite Drop and Recreate Table

3 Answers3