How can I reset auto-increment sequence number in sqlite

Question

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

EDIT

I cannot find the ORLite tool for this, so instead I use a simple SQL query. In my extends OrmLiteSqliteOpenHelper class, I use SQLiteDatabase to create this update.

EDIT2;)

In my project, I save the Lesson class and the WeekDefinition class.

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 increases. 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 adds this row to sqlite_sequence (when using auto-increment)

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

Now I delete all rows from the Lesson and WeekDefinition tables. After that, Lesson and WeekDef are empty, but sqlite_sequence is still the same. And this is a problem because the id in the table lesson starts with a value of 4 (seq from sqlite_sequence for the lesson and adds 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 have to get weekDefinitios, but in weekDefinitions classes_id only has a value of 1 to 3. And this is my problem. I need a sqlite_sequence "reset" table (or is there a better solution?)

Answer 1

Based on Marcos Vasconcelos' answer :

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

This query will set seq highest value in the col identification column in the Tbl table, so there is no risk of breaking the constraints.

Answer 2

Inside your .db file there is a table called sqlite_sequence

Each row has two columns name , which is the name of the table seq an integer indicating the current last value in this table

You can upgrade it to 0

But be careful if your table uses this identifier as a unique identifier.

Answer 3

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

Answer 4

Oh, I get it now. If you want to issue general database commands in ORMLite, you can use the updateRaw method. See javadocs . For other commands, there is also executeRaw .

 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 can also drop and recreate the table:

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

I think the real question is why does your application depend on the extension number of this database? It really does not have to worry.

• How about not displaying the number at all, so it could be 1 or 1001, and your application doesn't matter?
• You will also never be able to delete the lessons, but perhaps add a hidden logical field. Therefore, if they are added repeatedly, the hidden field can be set to false, and Math will still be at id # 1.