Sqlite Reset Primary Key Field

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 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. 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



Leave a reply



Submit