Menu
I have this method which will remove all rows from a table but I also want it to reset the autoincrement so that when a new row is added it will start again. The SQL statement I'm using isn't working due to certain columns not existing. Am I doing it right?
private void rmvAll() {
SQLiteDatabase db = appts.getWritableDatabase();
db.delete(TABLE_NAME, null, null);
db.execSQL("DELETE FROM SQLITE_SEQUENCE WHERE NAME = " + TABLE_NAME);
}
352
SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table. We can auto increment a field value by using AUTOINCREMENT keyword when creating a table with specific column name to auto increment. The keyword AUTOINCREMENT can be used with INTEGER field only.
ALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.
#Deleting the Table Number Sequence. DELETE FROM `sqlite_sequence` WHERE `name` = 'table_name'; If a table sequence is deleted from the sqlite_sequence table (or it doesn't exist), SQLite would use the next available number for the ROWID when a new row is inserted in that table.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
you'll need single quotes around your table name, i.e.
db.execSQL("DELETE FROM SQLITE_SEQUENCE WHERE NAME = '" + TABLE_NAME + "'");
Building on dldnh's answer:
The following query will set seq to the largest value in the col identity column in the Tbl table, so there is no risk of violating constraints.
UPDATE sqlite_sequence SET seq = (SELECT MAX(col) FROM Tbl) WHERE name="Tbl"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
