SQLite error 'column _id is not unique' on when inserting into an empty table

Question

I have a table with 4 fields: _id, to, from, hidden

Upon trying to insert the following values:

_id=167 from=1311005879000 to=1311005879000 hidden=0

into my table, I got an SQLiteConstraintException stating that

'column _id is not unique (code 19)'

To find the cause for this problem I tried querying the size of the table and found it is 0, so I have no idea where this is coming from.

Maybe I didn't understand the error correctly?

Edit: some code!

try {
    mDatabase.insertOrThrow("groups", null,
            mContentValues);
} catch (SQLException e) {
    e.printStackTrace();
}

Creation SQL:

CREATE TABLE IF NOT EXISTS groups(_id LONG PRIMARY KEY,hidden INTEGER,from LONG,to LONG

Solution

'column _id is not unique (code 19)'

So you are violating UNIQUE Constraint. This is reason of SQLiteConstraintException. Your _id column is most likely primary key and primary keys have implicitly assigned UNIQUE constraint that say no two rows can have same primary key.

You are trying to insert duplicit _id that already is in db or PK is assigned to NULL.

I tried querying the size of the table and found it is 0, so I have no idea where this is coming from.

I think your query was broken because your Exception says everything and it cannot be thrown if somewhere is not a problem.

Update:

If you are not assigned NULL to PK and also your table has 0 records probably problem is here:

mDatabase.insertOrThrow("groups", null, mContentValues);

You are assigned NULL to ColumnHack as second param of insert() method that you shouldn't. In SQLite, each row must have at least one column specified. It needs one column that can be safe assigned to NULL.

Comments

[saarraz1]

My query: SELECT COUNT(*) FROM groups

[saarraz1]

So which column should I specify in my case?

[Simon Dorociak]

@saarraz1 if your column has not not null constraint. in your case: hidden, to or from. and can i have a question? for what you accepted Hoan's asnwer? that only copied text from somewhere?

[saarraz1]

I actually accepted it by mistake. I haven't had the chance to check either of your solutions yet, although Hoan's, if it works, will be easier to implement in my case, because I'll be using the same code for multiple tables.

[Simon Dorociak]

@saarraz1 ok man, you frighten me :- your error is clear how i mentioned in asnwer, so check it out and let me know.

[saarraz1]

let us continue this discussion in chat