CODEWITHSUNDEEP
androidsqlite
Wise
Wise

Reputation: 658

After deleting a row in SQLite, _ID of a new entry is not consecutive

I use this method to delete a row in my sqlite db:

db.execSQL("delete from "+TABLE_NUMS+" where _ID = '" + this.rowID + "'");

and then I update the rest of Ids to make my entries consecutive:

db.execSQL("UPDATE "+TABLE_NUMS+" set _ID = (_ID - 1) WHERE _ID > "+this.rowID);

And it works fine, but when I add new entries to my DB, the ID of the new entries still add as if the deleted entries existed, say I have 10 rows with IDs starting from 1 to 10, and then I delete number 5 and 6, the rows become 1 to 8, but the new entry's ID will be 11. So my IDs sequence would be 1 to 8 and 11. How can I fix this?

Upvotes: 4

Views: 8755

Answers (4)

Akhilesh Dhar Dubey
Akhilesh Dhar Dubey

Reputation: 2148

SQLite keeps the largest ROWID in the special SQLITE_SEQUENCE table. You can update that table as:

db.execSQL("UPDATE SQLITE_SEQUENCE SET seq = 0 WHERE NAME = '"+TABLE_NAME+"'");

OR

delete that table as:

db.delete("SQLITE_SEQUENCE","NAME = ?",new String[]{TABLE_NAME});

Upvotes: 0

Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table. You cam modify that sequence as:

UPDATE SQLITE_SEQUENCE SET seq = this.ID -1 WHERE name = TABLE_NUMS

The same functionality is asked in this question.

Upvotes: 3

Benito Bertoli
Benito Bertoli

Reputation: 25803

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order. http://www.sqlite.org/autoinc.html

This is how SQLite works.

If you really need to have the Ids consecutive don't use autoincrement. Insert the ids yourself. You can select MAX(_ID) first to get the last id (greatest value).

Upvotes: 3

Srichand Yella
Srichand Yella

Reputation: 4246

This is because you have autoincrement set on _ID when you created the table. So, every row you add will be given a number automatically unless you explicitly set it. If it is absolutely necessary that you need the _IDs in consecutive order, I recommend that you set it yourself instead of using autoincrement.

Here is how to reset it:

delete from your_table;    
delete from sqlite_sequence where name='your_table';

This will delete all your data and reset the sequence.

Upvotes: 1

Related Questions