1

PRAGMA foreign_keys is enabled.

Table foo has no explicit INTEGER PRIMARY KEY.

Table bar has foo_rowid INTEGER with FOREIGN KEY (foo_rowid) REFERENCES foo(rowid).

Row f in foo has a rowid of x and row b in bar has a foo_rowid of x.

If VACUUM changes f's rowid to y, does it change b's foo_rowid to y?

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

Jordan
  • 4,510
  • 7
  • 34
  • 42

2 Answers2

1

No, it does not for non-primary key rowid. Lets create table without primary key:

$ sqlite3 test.db
SQLite version 3.7.16.2 2013-04-12 11:52:43
sqlite> create table test(name varchar(20));
sqlite> insert into test values('a'),('b'),('c');
sqlite> select rowid,* from test;
1|a
2|b
3|c

So far so good. Let's delete something:

sqlite> delete from test where rowid=2;
sqlite> select rowid,* from test;
1|a
3|c

Wow, rowid was kept - this is promising! However, what happens after VACUUM?

sqlite> vacuum full;
sqlite> select rowid,* from test;
1|a
2|c
sqlite>

Oops, it got renumbered!

In other words, you must create true auto-increment column for this to work and keep your ids intact after VACUUM.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • 2
    This answer only shows that `VACUUM` can change `rowid`s; my question is: _if that happens_, does it change foreign keys to match? – Jordan Jun 08 '13 at 10:22
  • Does this imply that if I create a **non auto-increment** primary key integer column then the values in that column can still be changed after a `vacuum` operation? – Tom Charles Zhang Aug 13 '19 at 14:34
  • No, any physical column (one that you defined as part of your CREATE TABLE statement) cannot be changed by vacuum. – mvp Aug 13 '19 at 15:33
1

This is impossible because a rowid cannot be referenced by another table. If a column can be referenced by a foreign key, it cannot be changed by VACUUM.

Community
  • 1
  • 1
Jordan
  • 4,510
  • 7
  • 34
  • 42