I have units like kg litre (and many more) that I have defined as enums in my codebase. I'm trying to expose these as a constants table in sqlite via virtual tables.

I'm able to query select id, code from constants correctly and see all the data.

However I'm not able to add a foreign key that points to this virtual table. This sql fails:

create table item (
qty integer,
unit_id integer references constants(id)
);


insert into item (qty) values (5);

Is there something I'm doing wrong is is this simply not supported and I should avoid adding foreign keys to virtual tables?

It is logically inconsistent to allow a foreign key on a virtual table.

The whole point of virtual tables is that they do not contain data that is under the control of SQLite. That means that the contents of the virtual table can change without SQLite being aware. If there is a foreign key constraint on that table, and the table changes, there is no way for SQLite to know about this and raise an error if appropriate.