> We can uniquely identify an object, even if it is deleted later, without worrying a new object could occupy the same rowid
I don’t know anything about mobile and not sure if it applies here but if you call vacuum on sqlite, it rewrites row ids. Is there a way to prevent that? If you have a column “PRIMARY KEY INTEGER”, it is your row id and it won’t change on vacuum, otherwise if you rely on automatic row id, you may have a surprise later.
sqlite> CREATE TABLE t(x);
sqlite> INSERT INTO t(x) VALUES ('foo'), ('bar');
sqlite> SELECT rowid, x FROM t;
1|foo
2|bar
sqlite> DELETE FROM t WHERE x='bar';
sqlite> INSERT INTO t(x) VALUES ('baz');
sqlite> SELECT rowid, x FROM t;
1|foo
2|baz
But Dflag is using:
rowid INTEGER PRIMARY KEY AUTOINCREMENT
Thanks. Just to be clear, I think this is the scenario when you don’t set your primary key but other columns in your insert statement.
CREATE TABLE t(x INTEGER PRIMARY KEY, y);
INSERT INTO(y) VALUES(‘value’);
Otherwise, if you set primary key in your insert statements, you’ll be fine I guess.
I’m not worried about reusing ids but if ids change e.g on vacuum, it is a disaster. Because people use row id(via last_insert_row_id()) assuming it won’t change but vacuum changes it and now you have a “dangling id”, if you fetch data again with that id, either you won’t find the row or get some other random row.