skip to content

In Room, what does @Insert(onConflict = OnConflictStrategy.REPLACE) do to an existing row with the same key, and how does @Upsert differ?

level: middleimportance: must knowfreq 66%

answer

  1. replace is not update
  2. delete first, then insert
  3. cascades fire on the delete
  4. upsert reacts to a uniqueness error
  5. -1 means not inserted

basics

~20 s

REPLACE resolves the clash by deleting the existing row and inserting the new one, so ON DELETE CASCADE actions fire. @Upsert tries the insert and, on a uniqueness conflict, updates the row by primary key, returning -1 for updated rows.

solid answer

~40 s

Without `onConflict`, `@Insert` uses `OnConflictStrategy.ABORT`: a primary-key or unique clash fails the statement and throws. `REPLACE` hands the clash to SQLite's replace resolution, which **deletes** the conflicting row and inserts the new one, so a child table with `onDelete = ForeignKey.CASCADE` loses its rows and every column takes the incoming object's value. `IGNORE` keeps the old row and returns `-1` for the skipped one. `@Upsert` (Room 2.5.0) runs a plain insert and, when it fails on a uniqueness constraint, updates the existing row matched by primary key; the row is never deleted, and a `Long` return is `-1` when an update happened. For "save this note whether or not it exists", `@Upsert` is the safer default.

go deeper

for a junior

Know that the default is ABORT, that IGNORE returns -1 for skipped rows, and that @Upsert inserts or updates a row by its primary key.

for a middle

Explain that REPLACE deletes and reinserts, why that fires ON DELETE CASCADE, and how @Upsert's insert-then-update fallback differs.

for a senior

Pick the strategy per write path, spot attachments lost to REPLACE in a bug report, and know the secondary-unique-index edge of @Upsert.

for a principal

Argue for a team default such as @Upsert for editor saves and REPLACE only for childless cache tables, and make reviewers flag REPLACE elsewhere.

## The default: ABORT `@Insert` and `@Update` take an `onConflict` argument whose values live in `OnConflictStrategy`. A **conflict** is a write that would break a `PRIMARY KEY` or `UNIQUE` constraint: inserting a note whose `id` already exists, or one whose `slug` repeats a unique index. Without the argument Room uses `OnConflictStrategy.ABORT`: the statement fails, the transaction Room wraps around the call rolls back, and an exception reaches your code. On Room 2.x running over the SupportSQLite stack, that exception is an `android.database.sqlite.SQLiteConstraintException`. Room places SQLite's conflict clause matching the constant into the statement it generates; the special value `OnConflictStrategy.NONE` tells Room to generate no clause at all, which behaves like `ABORT` but leaves a trigger free to use its own `ON CONFLICT` handling. ## The strategies | Constant | On a clash | `@Insert` returning `Long` | |---|---|---| | `ABORT` (default) | statement fails, transaction rolls back, exception thrown | throws instead of returning | | `REPLACE` | conflicting row deleted, new row inserted | the new `rowid`, never `-1` | | `IGNORE` | new row skipped, existing row kept | `-1` for each skipped row | | `NONE` | no clause generated, behaves like `ABORT` | throws instead of returning | | `ROLLBACK`, `FAIL` | deprecated since Room 2.1 | use `ABORT` | `ROLLBACK` and `FAIL` stay deprecated in both majors: Room's documentation says they do not behave as intended with Android's SQLite bindings. ## What REPLACE really does `REPLACE` is not an update. SQLite resolves the clash by **deleting** every existing row that conflicts and then inserting the new one. In a notes app that matters in three ways: 1. If an attachments table references notes through a foreign key declared with `onDelete = ForeignKey.CASCADE`, the delete half removes those child rows, so "saving" a note silently drops its attachments. 2. Every column is written from the object you passed. A local-only column that a server copy does not carry, such as `archived`, takes whatever the incoming object holds. 3. When the clash is on a unique index rather than the primary key, the row that disappears can be a *different* note from the one you meant to save. `REPLACE` is still the right tool when the incoming object is the whole truth for that row and nothing hangs off it, such as a cache table refilled from the server. ## How @Upsert works `@Upsert`, added in Room 2.5.0, takes the same entity parameters as `@Insert`. The generated code does not use `REPLACE`: - It runs a plain insert for each object. - If SQLite rejects the insert with a uniqueness error (a primary-key or `UNIQUE` violation), Room catches it and runs an **update** of that object, matched by primary key. - Any other error, such as a `NOT NULL` violation, is rethrown. A `Long` return holds the new `rowid` for an inserted object and `-1` for one that was updated. Because the existing row is updated in place, nothing is deleted and no delete cascade fires. With a partial class, `@Upsert(entity = Note::class)` updates only the columns that class carries when the key already exists. One edge is worth knowing. A uniqueness error on a *secondary* unique index is caught the same way, and the follow-up update looks for the incoming object's primary key. When that key is new, the update matches no row, so nothing is written and the return value is `-1`. ## Choosing for the notes app - **Save from the editor** (the note may or may not exist yet): `@Upsert`. - **Refill a server cache table** with no children and no local-only columns: `@Insert(onConflict = OnConflictStrategy.REPLACE)` is acceptable. - **Create a note that must be new**, with a duplicate treated as a bug: the default `ABORT`, and let the exception surface. - **Seed rows only if absent**: `IGNORE`, checking for `-1` to learn which were skipped. Whichever you choose, declare the `Long` or `List<Long>` return when the caller needs to know what happened: the `rowid` versus `-1` distinction is the only signal Room gives about whether a row was inserted or not. ## Mistakes interviewers listen for - Calling `REPLACE` "insert or update"; it is delete-then-insert. - Expecting `REPLACE` to return `-1` for an existing row. - Assuming `@Upsert` compares columns and writes only the changed ones. - Reaching for `ROLLBACK` or `FAIL` to get a different rollback behaviour. - Forgetting that `@Update` takes its own `onConflict`, with the same `ABORT` default, for clashes on a unique index. - Assuming the two majors differ here: the strategies and `@Upsert`'s insert-then-update fallback work the same way in Room 2.x and Room 3.0, though the exception type can differ with the SQLite stack underneath.

  • Does @Update also take an onConflict argument, and when does it matter?
    Yes. `@Update` accepts `onConflict` with the same default, `ABORT`. It matters when the new values would collide with another row's unique index, for example renaming a note's slug to one already taken: `ABORT` throws, `IGNORE` skips that row, `REPLACE` deletes the other conflicting row.
  • How do you tell, from one @Insert call with IGNORE over a list, which notes were skipped?
    Declare the return type as `List<Long>` or `LongArray`. Room returns one value per object in order: the new `rowid` for each inserted note and `-1` for each one the conflict skipped, so you can pair the results with the input list.
  • How does @Upsert behave with a partial entity class?
    Declare `@Upsert(entity = Note::class)` with a data class holding a subset of columns. It must carry every non-null column that has no default value, and the primary key unless that key is auto-generated. A new key inserts the row; an existing key updates only the columns the partial class carries.

  • REPLACE updates the existing row in place, like an UPDATE statement.
  • An @Insert with REPLACE returns -1 when the row already existed.
  • @Upsert is just @Insert with OnConflictStrategy.REPLACE under another name.
  • IGNORE throws an exception that you have to catch for duplicates.
  • FAIL is the safer choice when you want only the failing row rolled back.