DEV Community


Constraints in SQLite FTS5 virtual table (unique, not null, constraints, compound)

patarapolw profile image Pacharapol Withayasakpunt ・1 min read

At first sight, it seems that this is impossible with FTS5, as you cannot declare anything other than variable names and tokenization (and UNINDEXED).

However, this is easily solved by

  1. Declare two tables, one for standard table, another for FTS5
  2. Insert into the standard table first
  3. Use a non-standard UPDATE statement
    INSERT INTO extra_q (id, chinese, pinyin, english, [type], description, tag)
    SELECT @id, @chinese, @pinyin, @english, @type, @description, @tag
    WHERE EXISTS (SELECT 1 FROM extra WHERE id = @id)
Enter fullscreen mode Exit fullscreen mode

You should get an idea of how you can fit into your customization.

Of course, using only one table is possible too, but it might be harder to INTEGRITY_CHECK or clean invalids / duplicates.

Discussion (0)

Editor guide