Deleting row in android SQLite

Question

I have a database with tables |ID|NAME| , and I have a function which deletes a row in the table .

public void deleteWord (int id ){
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_WORDS, KEY_ID + "="+id, null);
    db.close();
}

this is how I create my table

    String createSetTable = 
            "CREATE TABLE "+TABLE_SETS+"("
            +KEY_SET_ID+ " INTEGER PRIMARY KEY, " 
            +KEY_SET_NAME+ " TEXT) ";
    db.execSQL(createSetTable);

The problem is that this function deletes a row with the given ID , and that ID remains empty like an array .For example I have a table

|1|Mike|

|2|Jane|

|3|Dave|

|4|Kate|

after using 'deleteWord (2)' i get something like this and the whole algorithm of the program is messed .

|1|Mike|

|3|Dave|

|4|Kate|

So what can I do to update my ID every time I delete a row ? Is there a special query which does it automatically ??

score 2 · Answer 1 · answered Jan 17 '14 at 19:13

Your ID's are not supposed to change. ID's should stick to their respective rows the whole time. I suggest you to change your logic to expect those ID's to stay the same and leave the database alone, which is the right thing to do.

If you definitely have to change those ID's, then don't make your ID column a primary key by removing the primary key statement like follows:

String createSetTable = 
        "CREATE TABLE "+TABLE_SETS+"("
        +KEY_SET_ID+ " INTEGER, " 
        +KEY_SET_NAME+ " TEXT) ";
db.execSQL(createSetTable);

But now, every time you insert or delete a row, you will have to iterate and update all of your rows with the right ID's, which again, is a tedious and inefficient task I do not recommend.

score 0 · Answer 2 · answered Jan 17 '14 at 19:12

0

The problem in Autoincrement that applied on Row ID as mention here

http://www.sqlite.org/autoinc.html

to solve this problem try to make another ID field that you deal with it as primary key like that

ID|EmlpoyeeID|EmployeeName

answered Jan 17 '14 at 19:12

mohammed momn

3,230
1
20
16

score 0 · Answer 3 · edited May 23 '17 at 11:49

Not sure I understood correctly but here goes.

Presuming that you want to edit your PK so that your altered table ends up like so:

1, Mike 2, Dave 3, Kate

This question could shed some light on the subject: How to update primary key

You're not supposed to change the value of primary keys though as this would result in foreign keys on other tabels pointing at the wrong item. It is possible to update the foreign keys as well though. Tread carefully.

Deleting row in android SQLite

3 Answers3