sql - Why does referencing a SQLite rowid cause foreign key mismatch?

Question

SQLite version 3.7.9 2011-11-01 00:52:41
sqlite> PRAGMA foreign_keys = 1;
sqlite> CREATE TABLE foo(name);
sqlite> CREATE TABLE bar(foo_rowid REFERENCES foo(rowid));
sqlite> INSERT INTO foo VALUES('baz');
sqlite> SELECT rowid, name FROM foo;
1|baz
sqlite> INSERT INTO bar (foo_rowid) VALUES (1);
Error: foreign key mismatch

Why does this error occur? It is a DML error, but I don't know what's wrong because:

• foo exists.
• foo.rowid exists.
• foo.rowid is the primary key of foo and therefore constrained to uniqueness.
• bar.foo_rowid is one column, which matches the fact that foo.rowid is one column.

Answer 1

SQLite documentation is quite clear on foreign keys:

The parent key must be a named column or columns in the parent table, not the rowid.

(See here.)

You can't use rowid for this, so just define your own auto incrementing primary key for the table.