sqlite rowid after deleting rows

Question

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

Answer 1

I assume you already know a little about rowid, since you're asking about its interaction with the VACUUM command, but this may be useful information for future readers:

rowid is a special column available in all tables (unless you use WITHOUT ROWID), used internally by sqlite. A VACUUM is supposed to rebuild the table, aiming to reduce fragmentation in the database file, and may change the values of the rowid column. Moving on.

Here's the answer to your question: rowid is really special. So special that if you have an INTEGER PRIMARY KEY, it becomes an alias for the rowid column. From the docs on rowid:

With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

This makes your primary key faster than it would've been otherwise (presumably because there's no lookup from your primary key to rowid):

The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value.

Of course, when your primary key is an alias for rowid, it would be terribly inconvenient if this could change. Since rowid is now aliased to your application data, it would not be acceptable for sqlite to change it.

Hence, this little note in the VACUUM docs:

The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.

If you really really really absolutely need the rowid to change on a VACUUM (I don't see why -- feel free to discuss your reasons in the comments, I may have some suggestions), you can avoid this aliasing behavior. Note that it will decrease the performance of any table lookups using your primary key.

To avoid the aliasing, and degrade your performance, you can use INT instead of INTEGER when defining your key:

A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

Answer 2

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