The full-text search extensions of SQLite (FTS3, FTS4, FTS5) create shadow tables. E.g., if I create FTS5-table
CREATE VIRTUAL TABLE test USING fts5(textData);
that will also automatically create several real (non-virtual) tables, with test_content among them, which (apparently) stores the actual data inserted into the original virtual table test.
I know SQLite authors suggest that these shadow tables "should not be accessed directly by the user". But it's not clear from the docs if there are no any guarantee at all about behavior of these tables, or this advise concerns primarily attempts to directly INSERT or UPDATE on them. But what are the risks of reading from these tables?
Specifically - I need another (regular) table to have a FOREIGN KEY which references rowid of the virtual FTS5 table:
CREATE VIRTUAL TABLE test USING fts5(textData);
CREATE TABLE myTable (col1 INTEGER REFERENCES test(rowid));
I couldn't find hints on that in the docs, but my own experiments showed that foreign key just doesn't work here - I still can delete records from the test table even though they are referenced from myTable. However, if instead I do
CREATE VIRTUAL TABLE test USING fts5(textData);
CREATE TABLE myTable (col1 INTEGER REFERENCES test_content(id));
then everything seems to work as needed - I can't delete records from the virtual test table if they are referenced from myTable.
This works because, as follows from direct inspection, the rowid value of the test table is always equal to the id column value of the test_content table. Even when I specify rowid manually like in INSERT INTO test (rowid, value) VALUES (424242, 'foobar'); - then a new row appears in the test_content with the corresponding content and the corresponding id equal to 424242 (even if no preceding records were ever in the table).
So basically, my question is - is this correspondence between the rowid of an FTS5-table and the id of the corresponding <name>_content shadow table guaranteed to hold? Or might this break in some cases or in future versions?