I'm often using lookup tables (for enums for example), storing small integers
in large tables, instead of storing many times the same enum names in rows.
But doing this often and for typically low to very-low cardinality tables,
results in multiple pages used (because of indexes) for tiny tables, which
only gets worse for larger page sizes, making the overhead large for small DBs.
So I thought maybe I could try merging these tables, but I also want to
preserve foreign keys, if only as a QC by using `pragma foreign_key_check`.
So I tried below, but I'm getting conflicting results of
`pragma foreign_key_check` compared to the manuel check via a join.
What am I missing?
```
C:\Users\ddevienne>sqlite3
SQLite version 3.25.3 2018-11-05 20:37:38
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', 0), ('size', 'medium', 1), ('size', 'large', 2);
sqlite> create table t (color int, constraint color_fk foreign key('color', color) references enum(cat, val));
sqlite> insert into t values (0); -- valid value
sqlite> insert into t values (3); -- invalid value
sqlite> .mode col
sqlite> .header on
sqlite> pragma foreign_key_check;
table rowid parent fkid
---------- ---------- ---------- ----------
t 1 enum 0
t 2 enum 0
sqlite> PRAGMA foreign_key_list(t);
id seq table from to on_update on_delete match
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 0 enum color cat NO ACTION NO ACTION NONE
0 1 enum color val NO ACTION NO ACTION NONE
sqlite> select t.rowid, ifnull(e.name, 'NULL') from t left outer join enum e on e.cat = 'color' and e.val = t.color;
rowid ifnull(e.name, 'NULL')
---------- ----------------------
1 red
2 NULL
```
IMHO, rowid 1 shouldn't be listed by `pragma foreign_key_check`.
I haven't tried with a *virtual column* (i.e. a *generated column*) yet
to see if I can work-around the above (I need to use a newer SQLite for that)
but isn't the above suspicious already? Thanks, --DD