CODEWITHSUNDEEP
androidsqlite
ant2009
ant2009

Reputation: 22526

Create a unique constraint on conflict replace

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,

Upvotes: 5

Views: 7270

Answers (5)

Qw4z1
Qw4z1

Reputation: 3030

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.

Upvotes: 4

forcewill
forcewill

Reputation: 1647

EDIT the awnser bellow is incorrect as pointed out by corsair992

Remove the autoincrement it fails because of autoincrement algorithm to avoid duplicated usages of the column value and since ID is a primary key you don't need it (see bellow the reason why) as stated by the doc 

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, 
that changes the automatic ROWID assignment algorithm to prevent the reuse 
of ROWIDs over the lifetime of the database. 
In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs 
from previously deleted rows.

Since the replace works by deleting the previous row which the conflict occured then the AUTOINCREMENT algorithm would fail since the new insert would use one value previously used.

So thats a clear violation of what you intented by using on replace your basicly saying "ok i don't mind to reuse the values for this primary key" but with AUTOINCREMENT your are saying "I never want the same value to be reused for this primary key".

Note that you don't need to use AUTOINCREMENT since primary key is aliased to the ROWID

In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID 
(except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

https://www.sqlite.org/autoinc.html

Upvotes: -1

pleonasmik
pleonasmik

Reputation: 789

Based on the files generated from Green Dao (even if you are not using it generates valid sql lite code) I would use the following sintax:

To create the table

String constraint = ifNotExists? "IF NOT EXISTS ": "";
    db.execSQL("CREATE TABLE " + constraint + "'FottContract.TABLE' (" + //
            "'FottContract.Column.ID' INTEGER PRIMARY KEY ," + // 0: id
            "'FottContract.Column.FEED_NUMBER' TEXT NOT NULL UNIQUE," + // 1: text
            "'FottContract.Column.TITLE' TEXT," + // 2: title
            "'FottContract.Column.DESCRIPTION' TEXT," + // 3: description
            "'FottContract.Column.ID' IMAGE,");"); // 4: image

    // Add Index
     // Add Index
    db.execSQL("CREATE INDEX " + constraint + "IDX_" + FottContract.TABLE + "_"+ FottContract.TABLE + "ON FottContract.TABLE" +
            " (FottContract.Column.FEED_NUMBER);");

To do the inserts

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.insertWithOnConflict(FottContract.TABLE, null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);
    }
}

Upvotes: -1

Shobhit Puri
Shobhit Puri

Reputation: 26007

UPDATE

Instead of mDb.insert(..) have you tried using the insertWithConflict function? Something like:

/* Insert it*/
mDb.insertWithOnConflict(FottContract.TABLE, null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);

PREVIOUS THOGUHTS

(As pointed out by @corsair992 in comments, I think he is right in saying that the triggers are fired after deletion.)

Code seems to be fine but following might be the issue:

Possible issue:

  • Are you using version 3.6.18 or above? I am supposing you would be since it was release in 2009. If you see the release notes of SQL lite for version 3.6.18, it says:

    Delete triggers fire when rows are removed due to a REPLACE conflict resolution. This feature is only enabled when recursive triggers are enabled.

  • Similar thing is mentioned if you read details of REPLACE on the on Conflict Clause page it says:

    ... When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled.

Possible solution:

It might be that recursive_triggers are disabled. You can enable recursive triggers by running the following SQL query:

PRAGMA recursive_triggers='ON'

You can execute the query in your code by using same execSQL method that you are using in the sniplet given question. Checkout SQLite fire DELETE trigger on a REPLACE blog to see more. They guy who wrote the blog mentions that his team had similar issues. When they did a REPLACE into the table a duplicate entry would be entered into it.

So I am assuming the above might solve your problem. Hope this helps a bit.

Upvotes: 1

James Wald
James Wald

Reputation: 13744

However, when the insert runs it always adds the new records and ignoring the contraint.

It sounds like you are observing the expected behavior of REPLACE. This conflict resolution algorithm will first delete any existing rows with the given FEED_NUMBER value before the insert occurs. The insert will then create a new row, with a new ID, with the specified feed number. After that insert, the returned row ID will be the only row in the table that contains that particular feed number.

You can only end up with multiple rows containing the same feed number if the value is NULL. From https://www.sqlite.org/lang_createtable.html#uniqueconst:

For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs.

Upvotes: 0

Related Questions