SQLite and unique rowid … Something you really need to know.

I posted this article in my old blog on Sunday, January 21, 2007 but I think it’s so important that I decided to re-post it again here.

If you don’t want to read the whole post then just do this: Every time you create a table with sqlite make sure to have an INTEGER PRIMARY KEY AUTOINCREMENT column (the rowid column will be an alias to this one).

If you have some time then read on…

A lot of people don’t realize that a rowid can change. As always a simple example worths more than 1000 words. You can test this example with SQLiteManager.

Create a table without a primary key:
CREATE TABLE test (name TEXT);

Insert some data into the table:
INSERT INTO test (name) VALUES (‘marco’);
INSERT INTO test (name) VALUES (‘giuly’);
INSERT INTO test (name) VALUES (‘gregory’);
INSERT INTO test (name) VALUES (‘house’);

Perform a SELECT rowid,* FROM test;
Here you go the result:
1 marco
2 giuly
3 gregory
4 house

Everything seems OK (until now…)
Now delete a couple of rows:
DELETE FROM test WHERE name=’marco’;
DELETE FROM test WHERE name=’gregory’;

Now perform again: SELECT rowid,* FROM test;
Here it is the result:
2 giuly
4 house

Everything is fine, no?
That’s cool…

Now, perform a VACUUM on the database and run again the query:
SELECT rowid,* FROM test;
Here it is the result:
1 giuly
2 house

Rowids are changed!!!! So please take extra care when you define a table and need to reference records using rowids.
From the official documentation: “Rowids can change at any time and without notice. If you need to depend on your rowid, make it an INTEGER PRIMARY KEY, then it is guaranteed not to change”. And I add also AUTOINCREMENT so you are sure that the same rowid(s) are not reused when rows are deleted.

Tags: sqlite

13 Comments

lux

January 28, 2012 Reply

you dont need to write AUTOINCREMENT in sqlite because a primary key already INCREMENTs by standart.
marco

January 28, 2012 Reply

This is not true. If you delete some rows and then VACUUM the database then sqlite will reuse some of the delete rowid values. So you'll lost uniqueness if you don't add the AUTOINCREMENT constraint.
Naim

August 13, 2012 Reply

RowID isn't a PRIMARY KEY. PRIMARY KEYs don't change.!
shade

January 10, 2013 Reply

I'm new to sqlite, but I did small experiment and instead

CREATE TABLE test (name TEXT);

i created table:

CREATE TABLE test (name TEXT UNIQUE);

after adding UNIQUE rowid do not change after doing VACUUM. As you can see I don't have INTEGER PRIMARY KEY in my schema, but it works. So why does it behave like INTEGER PRIMARY KEY?
Siva

June 26, 2013 Reply

Everything is understandable? Thanks for that.
But, VACUUM means?
marco

June 26, 2013 Reply

http://www.sqlite.org/lang_vacuum.html
meneo

November 16, 2013 Reply

Hello, Marco!
Thx for you post.
But I have met the problem...
Table created:
CREATE TABLE "matches" ("MatchID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE ,"League" TEXT,"Match" TEXT,"DT" DATETIME)

Also I have
CREATE UNIQUE INDEX "MainIndex_Match" ON "matches" ("League" ASC, "Match" ASC)

So. when I do several times
INSERT OR REPLACE INTO matches (League,Match) VALUES ("Test","Test")
Replace changes my MatchID... :( Why? Can I avoid it?

Thank you
William

April 27, 2014 Reply

I can't find any SQLite documentation page that contains the quoted text about ROWID values changing at any time and without notice.

At the lang_vacuum page (linked above), there's this:

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

That seems to be the only mention anywhere of ROWID values changing.

Where is the documentation page that contains the text you quoted?
- Brian
  
  October 18, 2019 Reply
  
  https://www.sqlite.org/rowidtable.html
  "If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY."
Pero

February 4, 2016 Reply

If you assign a field such as (id INTEGER PRIMARY KEY) - this field will become an alias for ROWID but ROWID will not change. So only if you don't have an alias for ROWID (such as "id" field in the table) which is by-the-way the usual way of using SQL tables - only then ROWID will change.
Pero

February 4, 2016 Reply

AUTOINCREMENT doesn't have anything to do with row id uniqueness. It has to do with reusing of ID value.

AUTOINCREMENT only serves to make sure you generate new and unused id for the new row.

if you create a row with ID 123 and then delete it, without AUTOINCREMENT it may be reused so if you insert again ID 123 may be used again at later time. With AUTOINCREMENT - it won't.

It will use different ID (that has ever before existed in that same table).

AUTOINCREMENT also won't touch anything which already existed so if you delete ID 123 and then add it again with the same ID, AUTOINCREMENT won't change - it only changes when you add higher value ID than has existed previously in the table.
Brent Rittenhouse

September 28, 2017 Reply

Regarding AUTOINCREMENT, straight from the SQLite documentation (@https://sqlite.org/autoinc.html)":

"1. The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed."
Tobi

June 23, 2018 Reply

You should really change this post. The comment section explained already that this is wrong, AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and the ROWID does not change when you specify an INTEGER PRIMARY KEY field. Only if you don't.