CODEWITHSUNDEEP
sqlsqlite
Nathan
Nathan

Reputation: 5243

SQLite Reset Primary Key Field

I have a few tables in SQLite and I am trying to figure out how to reset the auto-incremented database field.

I read that DELETE FROM tablename should delete everything and reset the auto-incremement field back to 0, but when I do this it just deletes the data. When a new record is inserted the autoincrement picks up where it left off before the delete.

My ident field properties are as follows:

Does it matter I built the table in SQLite Maestro and I am executing the DELETE statement in SQLite Maestro as well?

Any help would be great.

Upvotes: 169

Views: 155746

Answers (6)

charles young
charles young

Reputation: 2289

I realize this answer is coming very late, but I am building a .Net Maui app and faced the same issue. I am using the sqlite-net-pcl nuget and solved this problem by simply dropping the table and creating it again. The primary key is set back to 1.

       database.DropTableAsync<Waypoint>();
       database.CreateTableAsync<Waypoint>().Wait();

Upvotes: 0

Ricardo M.
Ricardo M.

Reputation: 1

If you are working with python and you want to delete all records from some table and reset AUTOINCREMENT.

You have this table

tables_connection_db.execute("CREATE TABLE MY_TABLE_DB (id_record INTEGER PRIMARY KEY AUTOINCREMENT, value_record real)")

So if you had added some records

connection_db=sqlite3.connect("name_file.db")
tables_connection_db=connection_db.cursor()

tables_connection_db.execute("DELETE FROM  MY_TABLE_DB ") # delete records
connection_db.commit()

name_table="MY_TABLE_DB"
tables_connection_db.execute("UPDATE sqlite_sequence SET seq=1 WHERE name=? ",(name_table,))
connection_db.commit()

connection_db.close()

Upvotes: 0

Huỳnh Ngọc Bang
Huỳnh Ngọc Bang

Reputation: 1732

You can reset by update sequence after deleted rows in your-table

UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='table_name';

Upvotes: 99

UmAnusorn
UmAnusorn

Reputation: 11184

If you want to reset every RowId via content provider try this

rowCounter=1;
do {            
            rowId = cursor.getInt(0);
            ContentValues values;
            values = new ContentValues();
            values.put(Table_Health.COLUMN_ID,
                       rowCounter);
            updateData2DB(context, values, rowId);
            rowCounter++;

        while (cursor.moveToNext());

public static void updateData2DB(Context context, ContentValues values, int rowId) {
    Uri uri;
    uri = Uri.parseContentProvider.CONTENT_URI_HEALTH + "/" + rowId);
    context.getContentResolver().update(uri, values, null, null);
}

Upvotes: 0

Daniel Lefebvre
Daniel Lefebvre

Reputation: 374

As an alternate option, if you have the Sqlite Database Browser and are more inclined to a GUI solution, you can edit the sqlite_sequence table where field name is the name of your table. Double click the cell for the field seq and change the value to 0 in the dialogue box that pops up.

Upvotes: 7

Nick Dandoulakis
Nick Dandoulakis

Reputation: 43150

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.

Upvotes: 320

Related Questions