foreigns key in android fts4 tables

Question

I have the following sql tables

Parent(_id,name) Kid(_id,name,parent_id)//where parent_id is foreign key Toy(_id,name,…,kid_id)//where kid_id is foreign key

right now I have queries that get all the Kids of a given Parent and also all the Toys for a given Kid.

There is a new need to include a search feature in the app. For that I need to change to an FTS4 table. How do I define the fts4 table so as not to lose the foreign key lookup capability? I am not experienced in fts by any margin. But I have read about docid vs rowid. Can I use docid for the foreign key values? Will someone please provide an example such as

CREATE TABLE Kid USING fts4(rowid, name, docid); //where docid is actually parent_id

Answer 1

FTS tables are not really like real tables; think of them as indexes.

FTS tables should contain only text columns; storing the parent_id in an FTS table does not make sense. Furthermore, the docid is the same as the rowid, and should be the same as the primary key of the 'real' table.

So the tables should look like this:

CREATE TABLE Kid (
    _id INTEGER PRIMARY KEY,
    name TEXT,
    parent_id INTEGER REFERENCES Parent(_id)
);
CREATE VIRTUAL TABLE Kid_FTS USING FTS4 (name);