SQLite Reset Primary Key Field
Try this:
delete from your_table;
delete from sqlite_sequence where name='your_table';
SQLite Autoincrement
SQLite keeps track of the largest
ROWID that a table has ever held using
the specialSQLITE_SEQUENCE
table. The
SQLITE_SEQUENCE
table is created and
initialized automatically whenever a
normal table that contains an
AUTOINCREMENT column is created. The
content of the SQLITE_SEQUENCE table
can be modified using ordinary UPDATE,
INSERT, and DELETE statements. But
making modifications to this table
will likely perturb the AUTOINCREMENT
key generation algorithm. Make sure
you know what you are doing before you
undertake such changes.
Reset INTEGER PRIMARY KEY AUTOINCREMENT in Android
Deleteing the rows won't reset ROWID.
SQLite documentation states:
SQLite keeps track of the largest ROWID that a table has ever held
using the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is
created and initialized automatically whenever a normal table that
contains an AUTOINCREMENT column is created. The content of the
SQLITE_SEQUENCE table can be modified using ordinary UPDATE, INSERT,
and DELETE statements. But making modifications to this table will
likely perturb the AUTOINCREMENT key generation algorithm.
This sql should do what you need:
UPDATE SQLITE_SEQUENCE SET seq = <n> WHERE name = <table>
Where n is the sequence you wish to set and table is your table name
How to reset the Primary key and delete the SQLite Db created
With this queries you can clear the table & then reset auto increment column.
using (var db = new SQLiteConnection(ApplicationData.Current.LocalFolder.Path + "\\db.sqlite"))
{
db.Query<your_table_name>("delete from your_table_name", "");
db.Query<sqlite_sequence>("delete from sqlite_sequence where name = ?", "your_table_name");
}
You also have to add this class.
public class sqlite_sequence
{
public string name { get; set; }
public int seq { get; set; }
}
Reset rowid field after deleting rows
Thanks for all your answers i found what i was looking for and it quite simple
DELETE FROM 'table_name' WHERE col='value';
REINDEX 'table_name';
and this will rebuild the specified table index, so if all the rows are deleted the index is reset to 0, and if is still row in the table, the index will be reordred correctly.
Related Topics
Select N Random Rows from SQL Server Table
MySQL: Alternatives to Order by Rand()
Is the SQL Where Clause Short-Circuit Evaluated
How Rownum Works in Pagination Query
How to Get Multiple Counts With One SQL Query
If' in 'Select' Statement - Choose Output Value Based on Column Values
How to Select the Nth Row in a SQL Database Table
How to Update Two Tables in One Statement in SQL Server 2005
Auto Increment After Delete in MySQL
How to Find Similar Results and Sort by Similarity
How Important Is the Order of Columns in Indexes
Multiple Insert Statements Vs. Single Insert With Multiple Values