4

I am trying to create a virtual table in sqlite3 using fts3 and having a column as autoincrement but when inserting values, the column is not getting populated. Code:

CREATE VIRTUAL TABLE contact
USING fts4(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT);

WHen inserting : insert into contact (name) values ('abc') the 'id' field is not getting incremented and the data in the table looks only
|abc

Is autoincrement not supported in fts3 of sqlite3 ?

Regards,
John

Donal Fellows
  • 133,037
  • 18
  • 149
  • 215
user704045
  • 41
  • 1
  • 2

2 Answers2

4

I found the answer to this question in another question here

The short answer is to use the column "rowid" (Special column) for id's of fts3 tables. Not sure if you can reference the rowid column as i haven't tried that.

Hope this solves your problem. :)

Community
  • 1
  • 1
CoolMcGrrr
  • 774
  • 8
  • 21
1

When creating an FTS 'Virtual Table' it ignores everything but the column names (e.g. auto increment, primary key, unique, and even Integer). Everything to an FTS table is just text. It reads those keywords in without error, but they are just 'sugary syntax' per the documentation.

http://www.sqlite.org/fts3.html

sean262 none
  • 212
  • 2
  • 8