In the SQLite documentation it says:
The parent key of a foreign key constraint is not allowed to use the rowid. The parent key must used named columns only.
The parent key must be a named column or columns in the parent table, not the rowid.
But does that apply to an alias of the rowid? For example in SQLite if you have a INTEGER PRIMARY KEY column then that column is essentially an alias of the rowid:
With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key".
(Exception omitted; not relevant here)
There is a similar question here: sql - Why does referencing a SQLite rowid cause foreign key mismatch? - Stack Overflow
If I take that example and modify it to use the alias (my integer primary key column) it appears to work:
sqlite> CREATE TABLE foo(a INTEGER PRIMARY KEY, name);
sqlite> create table bar(foo_rowid REFERENCES foo(a));
sqlite> INSERT INTO foo VALUES( NULL, "baz" );
sqlite> select * from foo;
a name
---------- ----------
1 baz
sqlite> INSERT INTO bar (foo_rowid) VALUES(1);
sqlite> select * from bar;
foo_rowid
----------
1
sqlite>
But is it legal to reference an alias of the rowid? Thanks.