如何在sqlite中重置自动增量序列号 [英] How can I reset a autoincrement sequence number in sqlite

问题描述

如何在 Ormlite 中更新表 sqlite_sequence?我只需要更新序列.如何通过 ORMLite 获取该表?

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

编辑

我找不到 ORLite 工具来执行此操作，因此我使用简单的 sqlite 查询.在我的类扩展 OrmLiteSqliteOpenHelper 中，我使用 SQLiteDatabase 进行更新.

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

在我的项目中，我坚持 class Lesson 和 class WeekDefinition.

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

现在，当我添加新课程时，id 是递增的.例如

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

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

并且在 weekDefinition 中:

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 将此行添加到 sqlite_sequence(使用自动增量时)

SQLite add this row into sqlite_sequence ( when use autoincrement )

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

现在，我从表 Lesson 和 WeekDefinition 中删除所有行.之后 Lesson 和 WeekDef 为空，但 sqlite_sequence 仍然相同.这是问题，因为表课程中的 id 从值 4 开始(课程的 sqlite_sequence 中的 seq 并添加 1):

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

和周定义

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

对于课程 id = 4 ，数学我应该得到 weekDefinitios，但在 weekDefinitions 中，courses_id 的值仅从 1 到 3这是我的问题.我需要重置" sqlite_sequence 表(或者有更好的解决方案?)

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

推荐答案

以 Marcos Vasconcelos 的答案为基础::>

Building on Marcos Vasconcelos' answer:

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

此查询会将seq设置为Tbl表中col标识列中的最大值，因此不存在违规风险约束.

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.

这篇关于如何在sqlite中重置自动增量序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！