CODEWITHSUNDEEP
androidsqlitefts3
Jawa
Jawa

Reputation: 49

how to insert unique value into virtual table using (fts3)

i have created a virtual table.i the problem is i cannot insert unique value into my virtual table database using fts3. here is my create table code 

 public static final String DATABASE_CREATE =
            "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE + " USING fts3(" +
                    KEY_CUSTOMER + " text UNIQUE," +
                    KEY_NAME + " NOT NULL," +
                    KEY_ADDRESS1 + " NOT NULL," +
                    KEY_ADDRESS2 + " NOT NULL," +
                    KEY_CITY + " NOT NULL," +
                    KEY_STATE + " NOT NULL," +
                    KEY_ZIP + " NOT NULL," +
                    KEY_SEARCH + "," +
                    KEY_AMOUNT + " NOT NULL," +
                    " UNIQUE (" + KEY_CUSTOMER + "));";

here i want KEY_CUSTOMER as unique, it's not working i can store same name many times ...its ignoring my UNIQUE statement

Upvotes: 2

Views: 1616

Answers (2)

Jimmy Chen
Jimmy Chen

Reputation: 187

Just like this: Create two table:

CREATE VIRTUAL TABLE IF NOT EXIST table1 USING fts3(content)
CREATE TABLE IF NOT EXIST table2(docid INTEGER, -Other Columns-)

Unique key is in the table2, fts table just use for search Inset sql like this

indexDB.beginTransaction();
INSERT INTO table1 (content) VALUES (?)
INSERT INTO table2 (docid, 'other columns') VALUES (last_insert_rowid(), ?)
indexDB.setTransactionSuccessful();
indexDB.endTransaction();

docid is the foreign key of two table. the primary key of table2 will help you to unique the record

Upvotes: 1

CL.
CL.

Reputation: 180270

The documentation says:

If column names are explicitly provided for the FTS table as part of the CREATE VIRTUAL TABLE statement, then a datatype name may be optionally specified for each column. This is pure syntactic sugar, the supplied typenames are not used by FTS or the SQLite core for any purpose. 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.

And an FTS table does only full-text search queries efficiently. It is meant to be used as an index, not as a table.

If you want to use constraints, you have to store your data in a 'real' table, and use the FTS table only for full-text searches. (In that case, you might want to use an external content table.)

Upvotes: 1

Related Questions