INSERT OR IGNORE table with AUTOINCREMENT key in Android

Question

I am trying to insert data to a new empty table. But I keep getting error (error code 19: constraint failed). I think the problem may caused by 'INTEGER PRIMARY KEY AUTOINCREMENT'. Here is my code:

database.execSQL("CREATE TABLE IF NOT EXISTS contacts (cid INTEGER PRIMARY KEY AUTOINCREMENT, name varchar NOT NULL, user varchar NOT NULL, UNIQUE(user) ON CONFLICT REPLACE)");
...
String sql = "INSERT OR IGNORE INTO contacts ( name , user) VALUES (?, ?)";
database.beginTransaction();

SQLiteStatement stmt = database.compileStatement(sql);
stmt.bindString(1, name);
stmt.bindString(2, entry.getUser());
int i = (int)stmt.executeInsert();
stmt.execute();
stmt.clearBindings();
stmt.close();

// error: 06-11 20:50:42.295: E/DB(12978): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed

Anyone knows what wrong with the sql statement? How can I solve this problem? Thanks
I have read few articles on stackoverflow. But cannot find anyone post related to 'insert or replace' + 'INTEGER PRIMARY KEY AUTOINCREMENT'.

Answer 1

I think your code is doing exactly what it is supposed to be doing. Reading over your code it looks like you want it to ignore inserts where there is already something inserted. The error you are receiving is telling you that the insert has failed.

If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead. These cases include:

Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints. Inserting a NULL into a column with a NOT NULL constraint. Inserting a row to a partitioned table, but the values you insert don't map to a partition. - "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

I would recommend catching the SQLiteConstraintException or check before inserting to see if the data is already there. If you need some ideas on how to check if data has been inserted let me know, I have had to do this before. Hope this helps.

Answer 2

There is a good begining to end example of SQLite on Android written by Lars Vogella here

Inside there and down a little ways here is the string that he uses for creating a table:

// Database creation sql statement
private static final String DATABASE_CREATE = "create table "
        + TABLE_COMMENTS + "(" + COLUMN_ID
        + " integer primary key autoincrement, " + COLUMN_COMMENT
        + " text not null);";

I have not tried either his or yours just now but a few things I noticed that differ between his and yours are:

• He has no space between the table name and the opening parenthesis
• He has a semicolon inside of the SQL string. after the closing parenthesis.

I am not certain if those will fix it for you, but it would probably be a good start.