Tenho lido a documentação completa de pesquisa de texto no SQLite e tudo faz sentido, exceto que parece não haver acomodação para realmente poder vincular linhas FTS com linhas em outras tabelas. Acho que não quero colocar todas as colunas da minha entidade na minha tabela FTS porque não posso especificar tipos de dados (ou STRICT) para ela, e não preciso que todos os dados sejam indexados para um FTS. Então, eu queria usar um relacionamento FK para fazer um link 1:1 da minha tabela de entidade principal para sua tabela FTS, assim:
CREATE VIRTUAL TABLE usersFTS USING fts4(
keywords,
nicknames
);
-- Create the users table with an explicit foreign key reference to usersFTS
CREATE TABLE users(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
usersFTS_id INTEGER NOT NULL,
FOREIGN KEY (usersFTS_id) REFERENCES usersFTS (rowid)
);
-- ... then:
-- Insert into usersFTS and capture the generated rowid for Alice's meta
INSERT INTO usersFTS (keywords, nicknames) VALUES ('software developer linux open-source', 'Ally Alice');
-- Capture the generated rowid for Alice
SELECT last_insert_rowid(); -- Assume it returns 1
-- Insert into users using the captured rowid
INSERT INTO users (name, email, usersFTS_id) VALUES ('Alice', '[email protected]', 1);
-- ... so that:
SELECT usr.id, usr.name, usr.email, fts.keywords, fts.nicknames
FROM usersFTS fts
INNER JOIN users usr ON usr.usersFTS_id = fts.rowid
WHERE fts.keywords MATCH '"software developer"';
Mas isso falha na INSERTafirmação:
Erro de lógica SQL: incompatibilidade de chave estrangeira - "users" referenciando "usersFTS"
Parece que uma tabela virtual não pode ter seu rowid referenciado em um relacionamento FK normal. Então, como posso vincular uma linha na minha tabela de pesquisa de texto completo a uma linha em outra tabela que contém mais dados sobre essa entidade? Existe alguma maneira de fazer isso no SQLite que mantenha a integridade referencial?
