And works fine when using generated columns. The only issue is that one cannot *hide* that *implementation detail* generated column from the `select *`. Virtual tables can have HIDDEN columns, which are selectable directly, but not via `select *`. I've long wished something like that existing in general in SQLite, but given that generated columns are a *reduced form* of virtual tables, I really wish HIDDEN was supported on them. By using this technique, I can probably fit all my enums on a single page, if I forego the UNIQUE index on the name, which is often wrong anyway, having aliases for a given enum int-value is common. Maybe I should just make the PK on the 3 columns, that would work. The table and its PK are then one-and-the-same, and index-organized, so optimized for lookups, since writes will be uncommon to them. ``` C:\Users\ddevienne>sqlite3 SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table enum (cat text, name text, val int, UNIQUE(cat, name), PRIMARY KEY(cat, val)) WITHOUT ROWID; sqlite> insert into enum values ('color', 'red', 0), ('color', 'green', 1), ('color', 'blue', 2); sqlite> insert into enum values ('size', 'small', 3), ('size', 'medium', 4), ('size', 'large', 5); sqlite> create table t_size (size int, size_cat text generated always as ('size') virtual, foreign key (size_cat, size) references enum(cat, val)); sqlite> insert into t_size values (0), (3); sqlite> .mode col sqlite> .header on sqlite> pragma foreign_key_check; table rowid parent fkid ---------- ---------- ---------- ---------- t_size 1 enum 0 sqlite> PRAGMA foreign_key_list(t_size); id seq table from to on_update on_delete match ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 0 enum size_cat cat NO ACTION NO ACTION NONE 0 1 enum size val NO ACTION NO ACTION NONE sqlite> select t.rowid, ifnull(e.name, 'NULL') from t_size t left outer join enum e on e.cat = 'size' and e.val = t.size; rowid ifnull(e.name, 'NULL') ---------- ---------------------- 1 NULL 2 small sqlite> select * from t_size; size size_cat ---------- ---------- 0 size 3 size sqlite> ```