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

December 21st, 2010 | 7 Comments »

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.

7 Responses to “SQLite and unique rowid … Something you really need to know.”

  • lux says:

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

  • marco says:

    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 says:

    RowID isn’t a PRIMARY KEY. PRIMARY KEYs don’t change.!

  • shade says:

    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 says:

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

  • meneo says:

    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

Leave a Reply

This field is required

This field is required