SQLite Forum

Multi-column Foreign Key with one "hard-coded" column value
Login
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>
```