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