Search code examples
sqliterowid

Reset rowid field after deleting rows

I'm using an sqlite database, and i want to reset rowid field after deleting rows. my table doesn't have a primary key or a specified Id, i'm only using the default rowid given by sqlite.

  NOM        TYPE     CL     VAL   SP        TP

 "test1"    "test1"  "1"    "1"  "test1"    "test1"
 "test2"    "test2"  "2"    "2"  "test2"    "test2"
 "test3"    "test3"  "3"    "3"  "test3"    "test3"

When i delete one or multiple rows, i want the default rowid to reset automatically, so i have read some answers and found that we can do that by using sql_sequence table .

delete from sqlite_sequence where name='table_name'

the problem is that i can't find that table in my sqlite 

[ no such table: sqlite_sequence ]

Is there any solution to my problem

Solution

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