2

I need to reset the automatic incremental ID to 0. When I delete every record from the table, the data will be deleted, then adding new data onto the table, the numbering continues from where it was before. I tried

db.delete('table_name'); // works fine. but numbering continues.
....
db.execute('TRUNCATE TABLE table_name'); // shows exception

The exception I get is:

DatabaseException(near "TRUNCATE": syntax error (code 1 SQLITE_ERROR):

How to fix this ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John
  • 133
  • 1
  • 6
  • Check This : https://stackoverflow.com/questions/9599741/how-to-delete-all-records-from-table-in-sqlite-with-android – Yashraj Sep 25 '21 at 07:49
  • No problem in deleting records. The problem is, after deleting all records, the auto - incremented id still incrementing from where it was before. And in my case db.execSQL("delete from "+ TABLE_NAME); command not works. – John Sep 25 '21 at 08:46
  • Does this answer your question? [Set start value for AUTOINCREMENT in SQLite](https://stackoverflow.com/questions/692856/set-start-value-for-autoincrement-in-sqlite) – Mark Benningfield Sep 25 '21 at 14:05

1 Answers1

0

This will reset the sequence index for the table you specify

await db!.delete('table_name');
await db!.update('sqlite_sequence', {'seq':1}, where: 'name = ?', whereArgs: ['table_name']);

To query all tables in sqlite schema you can use:

    Future<List<Object>> showTables() async {
       List<Object> tableNames = [];

       List<Map<String, Object?>> tables = await db!.rawQuery("SELECT * FROM sqlite_master WHERE type=?", ['table']);

       for (Map<String, Object?> table in tables) {
         tableNames.add(table['name']!);
       }

       return tableNames;
    }
Antonycx
  • 209
  • 3
  • 14