Create a unique constraint on conflict replace

Question

Android Studio 0.8.11

Hello,

I have the following unique constraint on the FEED_NUMBER column. So when I get new records that need to be inserted, I will just replace the records with the same FEED_NUMBER. However, when the insert runs it always adds the new records and ignoring the contraint. Have I done something incorrect here?

Here is my onCreate:

public void onCreate(SQLiteDatabase db) {
         String sql = "create table " + FottContract.TABLE
                + "(" + FottContract.Column.ID + " integer primary key autoincrement, "
                 + FottContract.Column.FEED_NUMBER + " text, "
                 + FottContract.Column.TITLE + " text, "
                 + FottContract.Column.DESCRIPTION + " text, "
                 + FottContract.Column.IMAGE + " text, "
                 + "unique (" + FottContract.Column.FEED_NUMBER + ") on conflict replace" + ")";

        db.execSQL(sql);
    }

And this is how I insert:

 private void saveToSQLite() {
        ContentValues contentValues = new ContentValues();

        /* For each item in the memory database insert into sqlite */
        for (int i = 0; i < mNewsFeedDB.size(); i++) {
            contentValues.put(FottContract.Column.FEED_NUMBER, mNewsFeedDB.get(i).getId());
            contentValues.put(FottContract.Column.TITLE, mNewsFeedDB.get(i).getTitle());
            contentValues.put(FottContract.Column.DESCRIPTION, mNewsFeedDB.get(i).getDescription());
            contentValues.put(FottContract.Column.IMAGE, mNewsFeedDB.get(i).getImage());

            /* Insert it */
            long row = mDb.insert(FottContract.TABLE, null, contentValues);
        }
    }

Many thanks for any suggestions,

Answer 1

EDIT with solution: This problem annoyed me so I decided to create a little test project with the same code as in the question. So the first thing to notice is that the UNIQUE constraint is respected. There is only one row in the database with a given FeedNumber. However the ID of this row changes with every INSERT on count of the ON CONFLICT REPLACE on the UNIQUE constraint (the existing row gets deleted and replaced with the new row) and AUTOINCREMENT on the ID column (sets ID to highest ID+1).

Secondly I tried to insert with an existing ID, which of course failed, since you can't insert two rows with the same PRIMARY ID.

So the solution is to first check if the row exists (try to insert on id) and if it fails (ie row == -1) use the update method instead. This obviously assumes that you actually get the correct row id from the mNewsFeedDB.get(i).getId() method.

Here is a link to my test project if anyone is interested in how I tested it. Not the prettiest code, but it does the job.

---

I would like to see a print out of the table before and after the inserts, but this line strikes me as a little fishy:

contentValues.put(FottContract.Column.FEED_NUMBER, mNewsFeedDB.get(i).getId());

What you are doing here is getting the ID of a given NewsFeedItem and assigning that value the FEED_NUMBER field but is the ID really the same as an existing feedNumber? If the ID if null for example SQLite will treat every FEED_NUMBER as unique (since different null values are treated as different values all together).

The other thing I have had issues with before is reusing the ContentValues object. You may need to create a new Contentvalues object for every pass in the for loop.