SQLite Forum

Can FTS5 table be used as regular table?
Login

Can FTS5 table be used as regular table?

(1) By anonymous on 2020-11-14 23:49:07 [source]

Can a virtual FTS5 table be used as if it were a regular table, or are there restrictions on its use? Will triggers and check constraints work on them the same?

Is there anything that will not work correctly or at all on an FTS5 table?

Assuming no restrictions, is it preferred to use a regular table for storing data, and create an extra FTS5 virtual table just for searching specific columns? Or, can the FTS5 table completely replace a regular table when at least one column needs MATCH-ing performance?

(2) By Dan Kennedy (dan) on 2020-11-17 11:42:14 in reply to 1 [link] [source]

Can a virtual FTS5 table be used as if it were a regular table, or are there restrictions on its use? Will triggers and check constraints work on them the same?

No. Those things won't work with a virtual table. You cannot create indexes on a virtual table either.

Assuming no restrictions, is it preferred to use a regular table for storing data, and create an extra FTS5 virtual table just for searching specific columns? Or, can the FTS5 table completely replace a regular table when at least one column needs MATCH-ing performance?

If all you need are MATCH queries, rowid lookup or range queries and full table scans, then FTS5 tables are fine as is. If you really need extra indexes, triggers or constraints, then you can either maintain both an FTS5 and regular table and keep them in sync (simpler and more intuitive to do, but wastes some space) or else fool around with external content or contentless tables:

https://sqlite.org/fts5.html#external_content_and_contentless_tables