How can I reset a autoincrement sequence number in sqlite

Bandzio picture Bandzio · Apr 7, 2011 · Viewed 28.7k times · Source

How to update table sqlite_sequence in Ormlite ? I just need update seq. How can I get that table via ORMLite ?

EDIT

I can't find ORLite tool to do this, so instead I use simple sqlite query. In my class extends OrmLiteSqliteOpenHelper I use SQLiteDatabase to make that update.

EDIT2 ;)

In my project I persist class Lesson and class WeekDefinition.

class Lesson{
    @DatabaseField(generatedId=true)
    private int id;
    ...
}

class WeekDefinitions{
    @DatabaseField(generatedId=true)
    private int id;
    @DatabaseField(foreign=true, columnName="lesson_id")
    private Lesson lesson;
    ...
}

Now , when I add new lessons, id is increment. For example

id = 1 Math
id = 2 English
id = 3 Medicine

and in weekDefinition :

id = 1 lesson_id = 1  nr = 20
id = 2 lesson_id = 1  nr = 22
id = 3 lesson_id = 2  nr = 32
...
id = 12 lesson_id = 3  nr = 3

SQLite add this row into sqlite_sequence ( when use autoincrement )

rowId = 1   name = lesson         seq = 3
rowId = 2   name = weekDefinition seq = 12

Now, I delete all rows from tables Lesson and WeekDefinition. Lesson and WeekDef are empty after that, but sqlite_sequence is still the same. And this is problem because id in table lesson start from value 4 ( seq from sqlite_sequence for lesson and add 1 ) :

id = 4 Math
id = 5 English
id = 6 Medicine

and weekDefinition

id = 13 lesson_id = 1  nr = 20
id = 14 lesson_id = 1  nr = 22
id = 15 lesson_id = 2  nr = 32

and for lesson id = 4 , Math i should get weekDefinitios, but in weekDefinitions lessons_id has value only from 1 to 3 And this is my problem. I need "reset" sqlite_sequence table ( or there is better solution ?)

Answer

Trisped picture Trisped · Jan 12, 2013

Building on Marcos Vasconcelos' answer:

UPDATE sqlite_sequence SET seq = (SELECT MAX(col) FROM Tbl) WHERE name="Tbl"

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