主页 / dba / 问题 / 343939
Accepted
Jez
Asked: 2024-12-01 07:43:36 +0800 CST

如何将虚拟 FTS 表链接到 SQLite 中的另一个表并强制引用完整性？

  • 772

我一直在阅读 SQLite 中的全文搜索文档，这一切都是有道理的，只是似乎没有为实际能够将 FTS 行与其他表中的行链接起来做出任何调整。我猜我不想将所有实体的列都放入我的 FTS 表中，因为我无法为其指定数据类型（或 STRICT），并且我不需要为 FTS 索引所有数据。所以我想使用 FK 关系从我的主实体表到其 FTS 表进行 1:1 链接，如下所示：

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"';

但这失败于以下INSERT声明：

SQL 逻辑错误：外键不匹配 - “users”引用“usersFTS”

似乎虚拟表不能在正常的 FK 关系中引用其 rowid。那么，如何将我的全文搜索表中的一行链接到另一个包含该实体更多数据的表中的一行？有没有办法在 SQLite 中做到这一点并保持引用完整性？

sqlite

1 个回答

  1. Best Answer

    由于 FTS 表是虚拟表 - 您无法为其创建 FK。但您可以向其中添加任何其他字段并从中构建伪FK：

    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"';

    请注意，您无法在虚拟表或虚拟表之间创建真正的 FK。您可以添加foreign key约束，但它会被忽略，并且不会强制执行pragma foreing_keys=on;。虚拟表目前不具备此功能。但您仍有一个字段可以绑定到其他表。

    更好的方法是使用虚拟表virtual：将文本保存在真实表中，但从虚拟表中引用它。文档中有一个特殊章节，其中有一个清晰的示例：6.2.2. 外部内容 FTS4 表

    • 1

相关问题