CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Boycott A.I.
Boycott A.I.

Reputation: 18871

SQLite - integer PRIMARY INDEX constraint failing?

In my Android app, I create a FULLTEXT table like this:

CREATE VIRTUAL TABLE products USING fts3 (
    _id integer PRIMARY KEY,
    product_name text NOT NULL,
   ...
)

And I add this index:

CREATE INDEX product_name_index ON products (product_name)

The app populates the table with various products, each with a unique _id value.

However, when I then try to insert an already-existing product ID (using an _id value that is already in the table, but with a different product_name value) like this:

long rowId = db.insertOrThrow("products", null, contentValues);

a new row is added to the table (with a brand new rowId value returned)!

I expected the insertOrThrow command to fail, so where am I going wrong? Is it something to do with the fact that it's a FULLTEXT table or could the index I specified on the product_name column be messing things up somehow?

I read this section about INTEGER PRIMARY KEY, but unfortunately I'm none the wiser.

Update

When I try to perform the same operation on a standard (non-FULLTEXT) table, then the insertOrThrow command results in the expected SQLiteConstraintException.

Upvotes: 0

Views: 49

Answers (1)

MikeT
MikeT

Reputation: 56938

I think the issue might be that an FTS table has the concept of a docid and a rowid column and specifying null for the docid results in that being given a value.

as per :-

There is one other subtle difference between "docid" and the normal SQLite aliases for the rowid column.

Normally, if an INSERT or UPDATE statement assigns discrete values to two or more aliases of the rowid column, SQLite writes the rightmost of such values specified in the INSERT or UPDATE statement to the database.

However, assigning a non-NULL value to both the "docid" and one or more of the SQLite rowid aliases when inserting or updating an FTS table is considered an error. See below for an example.

1.3. Populating FTS Tables

Upvotes: 1

Related Questions