Sunday, May 5, 2013

Auto-increment column in Sqlite virtual table

Few days ago, I had trouble with getting value of auto-increment column in Sqlite FTS table. So here is my virtual table:

CREATE VIRTUAL TABLE student USING FTS4(id PRIMARY KEY INTEGER AUTOINCREMENT, name TEXT);
I thought we don't need to insert id and only need to insert name. Then when getting value of id, it is always empty.
After searching for a while, I found the solution: Sqlite defines a column name rowid as the auto-increment primary key of FTS table.
SELECT rowid, name * FROM student;

No comments:

Post a Comment