How can I reset a autoincrement sequence number in sqlite

Question

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 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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);
    }