SQLite Forum

Multi-column Foreign Key with one "hard-coded" column value
Login

Multi-column Foreign Key with one "hard-coded" column value

(1) By ddevienne on 2020-04-14 14:18:18 [link] [source]

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

(2) By David Raymond (dvdraymond) on 2020-04-14 14:52:07 in reply to 1 [source]

If you look in the diagram on the create table page for table constraints, the only thing that can go inside of the parenthesis is "column-name". Since SQLite is so forgiving of quoting style it's saying "color" is a valid column name in the child table, so that must be what they're referring to.

If you had tried something like ... foreign key ('col', color) references enum... you would get an error along the lines of Error: unknown column "col" in foreign key definition.

The virtual generated column idea does sound like a good one though.

(3) By ddevienne on 2020-04-14 15:08:29 in reply to 2 [link] [source]

Hmmm, I see, good point David. I keep on forgetting how lax SQLite is...
I'll make a few more tests later, to verify the above, and try vcol too.

(4) By ddevienne on 2020-04-14 15:37:16 in reply to 2 [link] [source]

Confirmed:

sqlite> create table t2 (sz int, constraint t2_sz_fk foreign key('size', sz) references enum(cat, val));
Error: unknown column "size" in foreign key definition

(5) By ddevienne on 2020-04-14 16:22:39 in reply to 2 [link] [source]

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>

(6) By ddevienne on 2020-04-14 17:08:20 in reply to 1 [link] [source]

Note that this technique, while working when using generated columns,
is really a poor-man's substitute to a real type-system that supports
enums, the way PostgreSQL does.

Given that sqlite_ prefixed tables are reserved, we could have SQLite
doing the heavy lifting behind the scenes, on inserts and selects.
Given how SQLite is flexibly typed as Richard likes to say, a new
enum(X) type which implicitly converts text to ints if it can, and stores
everything as-is otherwise, would be possible, w/o any format change.
It would just be one more of these features that if used, prevents older
SQLite version from using the DB, although not even in practice, since a
real value is always stored regardless, only known ints are implicitly
converted back to the enum text value on select, or the reverse on insert.

Could even hook into the subtype machinery used by function call chains.

Using enums would be both storage efficient, and user friendly.
I know, I know, SQLite is lite, and all...

Just thinking aloud :)