4 ms·
It's not just on vacuum. Rowids are reused whenever stuff is deleted from the end of the table: sqlite> CREATE TABLE t(x); sqlite> INSERT INTO t(x) VAL
by sltkr 6y ago
It's not just on vacuum. Rowids are reused whenever stuff is deleted from the end of the table:
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
This is true even if rowid is aliased by an INTEGER PRIMARY KEY. Whenever a row is inserted, the new rowid is simply 1 greater than the largest rowid in use (or 1 for an empty table).
But Dflag is using:
rowid INTEGER PRIMARY KEY AUTOINCREMENT
which prevents ids from being reused. So this isn't actually a problem for them, but the documentation could be clearer about it.
- tirrex 6y ago> This is true even if rowid is aliased by an INTEGER PRIMARY KEY. 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.