CODEWITHSUNDEEP
androidandroid-sqlite
Barry Fruitman
Barry Fruitman

Reputation: 12664

What kind of conflict(s) can happen with updateWithOnConflict()?

I understand inserting a row can cause a conflict if the row is a duplicate, but what kind of conflicts can an update cause? I can't think of any.

In other words, what is updateWithOnConflict() for?

Upvotes: 3

Views: 419

Answers (3)

MikeT
MikeT

Reputation: 57073

In other words, what is updateWithOnConflict() for?

The main difference between update and updateWithOnConflict is that the latter will allow actions to be taken if a conflict occurs (e.g. a constraint is violated), whilst the former will result in an exception (which could be trapped).

In short it provides a greater degree of control when a constraint is violated. However, you can also utilise a conflict-clause to very much do the same (although the conflict-clause doesn't provide the flexibility of being easily changed dynamically).

For example assume a table defined with :-

CREATE TABLE mytable (
    _id INTEGER PRIMARY KEY,           // IMPLIED CONSTRAINT UNIQUE NOT NULL
    thing_name TEXT UNIQUE,            // COLUMN CONSTRAINT UNIQUE
    table2_reference INTEGER NOT NULL, // COLUMN CONSTRAINT NOT NULL
    table3_reference INTEGER NOT NULL, // as previous
    CONSTRAINT table2_to_table3 UNIQUE // TABLE CONSTRAINT UNIQUE
        (table2_reference, table3_reference)
);

And populated with :-

enter image description here

And we have an update method, that caters for using both update and updateWithOnConflict such as :-

public void updateRow(
        long id,
        String newname,
        long newtable2ref,
        long newtable3ref,
        boolean with_on_conflict_flag) {
    if (id < 1) return;
    ContentValues cv = new ContentValues();
    if (newname != null && newname.length() > 0 ) {
        cv.put(COL_MYTABLE_THINGNAME,newname);
    }
    if (newtable2ref > 0) {
        cv.put(COL_MYTABLE_TBL2REF,newtable2ref);
    }
    if (newtable3ref > 0) {
        cv.put(COL_MYTABLE_TBL3REF,newtable3ref);
    }
    if (cv.size() > 0) {
        if (with_on_conflict_flag) {
            mDB.updateWithOnConflict(
                    TB_MYTABLE,cv,
                    COL_MYTABLE_ID + "=?",
                    new String[]{String.valueOf(id)},
                    SQLiteDatabase.CONFLICT_IGNORE //<<<< IGNORE exception
            );
        } else {
            mDB.update(
                    TB_MYTABLE,
                    cv, COL_MYTABLE_ID + "=?",
                    new String[]{String.valueOf(id)}
                    );
        }
    }
}

and then mDBHlpr.updateRow(1,null,1,2,????); is used twice

  • first with ???? as false
  • and then with true
  • i.e. a constraint violation is being forced as this would attempt to create a non-unique combination (the combination of table2_reference and table3_reference would match the 2nd row).

The first where just the update method is invoked would result in an exception and the App would stop e.g. :-

android.database.sqlite.SQLiteConstraintException: columns table2_reference, table3_reference are not unique (code 19)

The second traps the error ignoring it (as per the conflict-algorithm), so doing nothing (although the log will contain the stack trace for the error).

Note the action by the updateWithOnConflict method is dependent upon the 5th parameter (CONFLICT_IGNORE was used in the above). here's a list of the conflict-algorithms as er SQLiteDatabase:-

CONFLICT_ABORT

When a constraint violation occurs,no ROLLBACK is executed so changes from prior commands within the same transaction are preserved.

CONFLICT_FAIL

When a constraint violation occurs, the command aborts with a return code SQLITE_CONSTRAINT.

CONFLICT_IGNORE

When a constraint violation occurs, the one row that contains the constraint violation is not inserted or changed.

CONFLICT_NONE

Use the following when no conflict action is specified.

CONFLICT_REPLACE

When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row.

CONFLICT_ROLLBACK

When a constraint violation occurs, an immediate ROLLBACK occurs, thus ending the current transaction, and the command aborts with a return code of SQLITE_CONSTRAINT.

Upvotes: 1

Sam
Sam

Reputation: 86958

Conflicts can arise from many constraints: primary key, unique, foreign key (if enabled), etc.

Imagine a user trying to update a row's _id to an existing one. This would create a duplicate row _id which would lead to the same conflict as your inserting example.

SQLite Column Constraint Diagram

Just in case, update conflicts aren't just localized to Android's implementation of SQLite, it's fundamental to SQLite itself but you probably know this.

Upvotes: 1

edthethird
edthethird

Reputation: 6263

from the docs:

The ON CONFLICT clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints

If you update something it will fail if it you have a UNIQUE requirement but the update makes it no longer unique, or if you try to update a NOT NULL field with null. You can also have multiple primary keys on a table, and should never change those.

It also depends on how you are using it. Maybe you want your app to throw an exception so you can catch it and log it somewhere, or maybe you don't care and just want to use the CONFLICT_REPLACE flag so you don't have to think about it.

Upvotes: 1

Related Questions