Início / dba / Perguntas / 343939
Accepted
Jez
Asked: 2024-12-01 07:43:36 +0800 CST

Como vincular uma tabela FTS virtual a outra tabela no SQLite e impor integridade referencial?

  • 772

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?

sqlite

1 respostas

  1. Best Answer

    Como a tabela FTS é virtual, você não pode criar um FK para ela. Mas você pode adicionar qualquer campo adicional a ela e construir um pseudo FK a partir dele:

    create table users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE VIRTUAL TABLE usersFTS USING fts4(
    id INTEGER,
    keywords,
    nicknames
);

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

INSERT INTO usersFTS (id, keywords, nicknames)
   VALUES ((select id from users where name='Alice'),
          'software developer linux open-source', 'Ally Alice');

SELECT usr.id, usr.name, usr.email, fts.keywords, fts.nicknames
FROM usersFTS fts
INNER JOIN users usr ON usr.id = fts.id
WHERE fts.keywords MATCH '"software developer"';

    Observe que você não pode criar um FK real para ou a partir de uma tabela virtual. Você pode adicionar foreign keyuma restrição, mas ela seria ignorada e não seria aplicada com pragma foreing_keys=on;. A tabela virtual não tem essa capacidade atualmente. Mas você ainda teria um campo para vincular a outra tabela.

    Melhor ainda, seria ter uma tabela virtual virtual : manter os textos em uma tabela real, mas referenciá-la a partir da virtual. Há um capítulo especial na documentação com um exemplo claro: 6.2.2. Conteúdo externo Tabelas FTS4

    • 1

relate perguntas