Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 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 ?)

like image 652
Bandzio Avatar asked Apr 07 '11 19:04

Bandzio


People also ask

Does SQLite support Autoincrement?

SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table. We can auto increment a field value by using AUTOINCREMENT keyword when creating a table with specific column name to auto increment.

How does Autoincrement work in SQLite?

AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential. Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY.

How can reset primary key ID after delete the row?

So add one to that number and run the following command: ALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.

Does SQLite have sequences?

To create and access a sequence, you must use SQL functionality that is unique to the BDB SQL interface; no corresponding functionality exists in SQLite. The sequence functionality is implemented using SQLite function plugins, as such it is necessary to use the 'select' keyword as a prefix to all sequence APIs.


1 Answers

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.

like image 186
Trisped Avatar answered Sep 19 '22 16:09

Trisped