sqlite rowid after deleting rows
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.
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