How can I reset a autoincrement sequence number in sqlite

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 t开发者_JAVA技巧hat 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 ?)

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.

Inside your .db file there's an table called sqlite_sequence

Each row has two columns name which is the name of the table seq a integer indicating the current last value at this table

You can update it to 0

But beware if your table use this id as the unique identifier.

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

If you want to issue general database commands in ORMLite, you can use the updateRaw method. See the javadocs. There is also executeRaw for other commands.

lessonDao.updateRaw("delete from 'lesson';");
lessonDao.updateRaw("delete from sqlite_sequence where name='lesson';");
weekDefinitionDao.updateRaw("delete from 'weekdefinition';");
weekDefinitionDao.updateRaw(
    "delete from sqlite_sequence where name='weekdefinition';");

You could also drop and recreate the table as well:

TableUtils.dropTable(WeekDefinition.class);
TableUtils.dropTable(Lesson.class);
TableUtils.createTable(Lesson.class);
TableUtils.createTable(WeekDefinition.class);

I think the real question is why is your application depending on this database internal number? It really shouldn't care.

• How about not displaying the number at all so it can be 1 or 1001 and your application won't matter?
• You could also never remove the lessons at all but maybe add a hidden boolean field. So if they get re-added, the hidden field could be set to false and Math would still be at id #1.

This worked for me in my database : (I Set the id before one, then after deleting one row, when I add new data row again, the auto increment serial remain ok ) :

 public void  updateSerialNumber ( long memberId){
        String query = "UPDATE SQLITE_SEQUENCE SET SEQ= '"+(memberId-1)+"' WHERE NAME='"+ReportBigHelper.TABLE_MEMBER+"'";
        database.execSQL(query);
    }