How can I restart auto increment ID from 1 in an Android SQLite Database

Question

I have an Android SQLite Database and I inserted some rows. After I deleted these rows the ID column continues from the last ID and I would like to restart the counting from 1.

I know it's not what you asked, but why create headaches for yourself? The actual autogenerated values have no meaning, and presumably if you've deleted rows you've also deleted all dependent rows too, so why restart at 1? — Tim, Sep 07 '11 at 19:45
possible duplicate of [SQLite Reset Primary Key Field](http://stackoverflow.com/questions/1601697/sqlite-reset-primary-key-field) — David Snabel-Caunt, Sep 07 '11 at 19:46
Now I realized that I can resolve the problem even without start the ID count from 1. Thanks. — Alex, Sep 08 '11 at 07:40

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

5

Inside your .db file there's an table called sqlite_sequence

Each row has two columns 'name' which is the name of the table 'seq' a integer indicating the current last value at this table

You can update it to 0

But beware if your table use this id as the unique identifier.

Take a look at this answer: SQLite Reset Primary Key Field

edited May 23 '17 at 12:32

Community

1
1

answered Sep 07 '11 at 19:34

user634545

9,099
5
29
40

score 5 · Answer 2 · answered Sep 07 '11 at 19:35

5

Try:

delete from sqlite_sequence where name='your_table';

answered Sep 07 '11 at 19:35

JRL

76,767
18
98
146

score 1 · Answer 3 · answered Nov 22 '13 at 15:41

If you want to reset every RowId via content provider try this

    rowCounter=1;do {           
        rowId = cursor.getInt(0);
        ContentValues values;
        values = new ContentValues();
        values.put(Table_Health.COLUMN_ID,
                   rowCounter);
        updateData2DB(context, values, rowId);
        rowCounter++;

    while (cursor.moveToNext());

public static void updateData2DB(Context context, ContentValues values, int rowId) {
Uri uri;
uri = Uri.parseContentProvider.CONTENT_URI_HEALTH + "/" + rowId);
context.getContentResolver().update(uri, values, null, null);

}

How can I restart auto increment ID from 1 in an Android SQLite Database

3 Answers3