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

Question

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?

Why do you need consecutive numbers? You should live with this behavior, this is how it works. — biziclop, Jul 05 '12 at 14:46

score 3 · Answer 1 · answered Jul 05 '12 at 14:49

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).

score 3 · Accepted Answer · edited May 23 '17 at 12:17

3

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.

edited May 23 '17 at 12:17

Community

1
1

answered Jul 05 '12 at 14:49

Yaqub Ahmad

27,569
23
102
149

4

There should be no need to mess with the sequence / autoincrement, please take care with advice like this! – Nanne Jul 05 '12 at 14:50
@Nanne is right, you have to change your logic, modifying the autoincrement is not a good idea. – Yaqub Ahmad Jul 05 '12 at 14:55
Thank u guys for all the info. I'd better off with changing the logic then. – Wise Jul 05 '12 at 15:24

score 1 · Answer 3 · answered Jul 05 '12 at 14:47

1

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.

answered Jul 05 '12 at 14:47

Srichand Yella

4,218
2
23
24

There should be no real need for this in most cases, so unless there is a really definate reason, I would NOT recommend setting the id manually, ever. I just don't see the need. – Nanne Jul 05 '12 at 14:49

score 0 · Answer 4 · answered Jul 08 '14 at 06:54

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});

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

4 Answers4