不建議使用rowid作為sqlite主鍵

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

If you have some time then read on…

A lot of people don’t realize that a rowidcan change. As always a simple example worths more than1000 words. You can test this example withSQLiteManager.

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

SELECT rowid,* FROM test;

Here it is the result:

1 giuly

2 house

Rowids are changed!!!! So please take extra care when you define atable and need to reference records using rowids.

From the official documentation: “Rowids can change at any time andwithout notice. If you need to depend on your rowid, make it anINTEGER PRIMARY KEY, then it is guaranteed not to change”. And Iadd also AUTOINCREMENT so you are sure that the same rowid(s) arenot reused when rows are deleted.