How to reset autoincrement value after deleting 1 row of column from it, on sqlite java android?

on MYSQLI this method is work on me back then, ALTER TABLE tablename AUTO_INCREMENT = 0, this will reset on last autoincrement position

but i don’t know how to do it on SQLITE, so the idea is, i want to delete 1 row column table, and at the same time i want to reset auto increment value to the last position, here is my code

listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
        @Override
        public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
            getName = parent.getItemAtPosition(position).toString();
            getID = myDB.getID(getName);
            getIDtoString = String.valueOf(getID);

            AlertDialog.Builder builder = new AlertDialog.Builder(Notepad.this);
            builder.setMessage("You Want To Delete "+getName+" From Notepad?").setPositiveButton("Yes Please!", new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface dialog, int which) {
                    boolean isDeleted = myDB.deleteLol(getIDtoString,getName);
                    if (isDeleted) {
                        toastMessage("Delete Success!");
                        refresh();
                    } else {
                        toastMessage("Delete Failed!");
                    }
                }
            }).setNegativeButton("No, Don't Do That!", null);

            AlertDialog alertDialog = builder.create();
            alertDialog.show();

        return true;
        }
    });

and this is my DatabaseHelper class

public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table usersData(id integer primary key autoincrement, username text, email text, password text)");
    db.execSQL("create table notepadData(id integer primary key autoincrement, notepad text)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("drop table if exists usersData");
    db.execSQL("drop table if exists notepadData");
    onCreate(db);
}

public boolean deleteLol(String id,String notepad) {
    SQLiteDatabase myDB = this.getWritableDatabase();
    myDB.delete("notepadData", "id = ?",new String[] {id});
    myDB.execSQL("UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE notepad = '"+notepad+"'");// i found this on internet recently and doesn't work
    return true;
}

Answer

SQLite stores the last ROW-ID in a table SQLITE_SEQUENCE, which is managed by the SQLite automatically. The values within this table remain saved even if you delete or empty other tables.

There are two approaches to reset the auto-increment counter.

Delete your entire table and then recreate it. (maybe use a dummy temporary table to save the current data). Delete the information about your table from the SQLITE_SEQUENCE meta table.

DELETE from table;
DELETE from sqlite_sequence where name='table';

Update the sequence in the sqlite_sequence using update query

update sqlite_sequence set seq=5 where name='table';

Advertisement

Answer