CODEWITHSUNDEEP
javaandroidsqlsqlite
user3199577
user3199577

Reputation: 249

Deleting row in android SQLite

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 ??

Upvotes: 0

Views: 270

Answers (3)

Robin Dorbell
Robin Dorbell

Reputation: 1619

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.

Upvotes: 0

user692168
user692168

Reputation:

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.

Upvotes: 2

mohammed momn
mohammed momn

Reputation: 3210

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

Upvotes: 0

Related Questions