sqlite rowid after deleting rows

user_1108687 0
Viewed 11447

I created sqlite table tmp with columns id,name.

CREATE TABLE if not exists tmp (
            id             INTEGER PRIMARY KEY,
            name            TEXT NOT NULL);

I inserted 5 rows in it.

When I run select query with rowid column it gives below output.

select rowid,id,name from tmp;

rowid  id    name
1      1     a
2      2     b
3      3     c
4      4     d
5      5     e

Now I delete rows with id 3 and 4 and run above query again.

rowid  id    name
1      1     a
2      2     b
5      5     e

Now my problem is rowid not getting reset and holes.

Even after doing vacuum, it still doesnt reset rowid, it still gives above output.

The output which I want is

rowid id  name
1      1  a
2      2  b
3      5  e

Can anybody help me in achieving above output?

Thanks

sqlite
3 Answers

I found a solution for some case. I don't know why, but this worked. 1.Rename column "id" to any other name (not PRIMARY KEY) or delete this column because you have already "rowid".

CREATE TABLE if not exists tmp (
            my_i             INTEGER NOT NULL,
            name            TEXT NOT NULL);

2.Insert 5 rows in it.

select rowid,* from tmp;

rowid  my_i    name
1      1      a
2      2      b
3      3      c
4      4      d
5      5      e

3.Delete rows with rowid 3 and 4 and run above query again.

DELETE FROM tmp WHERE rowid = 3;
DELETE FROM tmp WHERE rowid = 4;
select rowid,* from tmp;
rowid  my_i    name
1      1        a
2      2        b
5      5        e

4.Run SQL

VACUUM;

5.Run SQL

select rowid,* from tmp;

The output:

rowid my_i  name
1      1    a
2      2    b
3      5    e
user_22766810
user_83636470
Related