qt - 使用带有唯一列的 fts3 使用 sqlite 创建虚拟表不会保持唯一

Question

我正在创建(在 Qt 中)

QString q = "CREATE VIRTUAL TABLE playlist USING fts3 ("
                        "from_user , " 
                        "from_id , "
                        "created_time , "
                        "created_time_formated , "
                        "user_id unique )";

然后执行

"INSERT OR IGNORE INTO  play_list (from_user,from_id,created_time,created_time_formated,user_id) SELECT '....','....','.....','....','123'"  

我正在使用全文搜索，所以这是我使用虚拟表的方式

如果我有另一行 user_id == 123 它仍然会插入该行，为什么？

Answer 1

FTS 文档 states那:

The same applies to any constraints specified along with an FTS column name - they are parsed but not used or recorded by the system in any way.

所以 UNIQUE约束被忽略。

您可以通过在 View 上使用触发器来模拟约束(不在表上，因为触发器在虚拟表上不可用)，然后在该 View 上插入行:

CREATE VIEW playlist_view AS SELECT * FROM playlist;

CREATE TRIGGER insert_playlist INSTEAD OF INSERT ON playlist_view
BEGIN
    SELECT RAISE(ABORT, 'column user_id is not unique') FROM playlist 
        WHERE user_id=new.user_id;
    INSERT INTO playlist (from_user, from_id, created_time, 
                          created_time_formated, user_id) 
    VALUES (NEW.from_user, NEW.from_id, NEW.created_time, 
            NEW.created_time_formated, NEW.user_id);
END;

-- And you do the insertion on the view 
INSERT INTO playlist_view (from_user,from_id,created_time,created_time_formated,user_id) SELECT ...;

因为 OR IGNORE不会忽略显式的 RAISE ，如果你想忽略错误，你必须替换 RAISE(ABORT,...)通过 RAISE(IGNORE) .