SQLiteConstraintException during Room insert retries

Direct Answer

How does the choice between IGNORE and REPLACE affect unrelated columns in a partial entity?

• IGNORE leaves the existing row untouched. All columns retain their current values; the attempted insert simply does nothing and returns -1.
• REPLACE performs a DELETE followed by an INSERT. Any columns not supplied in the new entity are set to their default values (or NULL if no default is defined). Thus, unrelated columns are overwritten, potentially losing data.

Can you detect if an insert was ignored vs another failure?

• With @Insert(onConflict = OnConflictStrategy.IGNORE), Room returns the row ID of the inserted row. If the value is -1, the insert was ignored due to a conflict. This is the only built‑in way to distinguish an ignored insert from a successful one.
• For REPLACE, the method returns the row ID of the newly inserted row; if a constraint error occurs that is not a primary‑key conflict (e.g., NOT NULL), Room throws a SQLiteConstraintException.
• There is no separate callback or event for a conflict; you must rely on the return value or catch the exception.

Typical Retry Pattern

1. Use IGNORE for idempotent writes when you only need to ensure the row exists and avoid duplicates.
2. Check the returned long value: -1 means the row was already present; any other value is the new row ID.
3. If you need to update certain columns without wiping others, use @Update or a custom @Query("UPDATE ...") instead of REPLACE.
4. Wrap the call in a retry loop only if the exception is a transient SQLite error (e.g., database locked). Do not retry on a -1 result.

Example DAO

@Dao
public interface MyDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    long insertOrIgnore(MyEntity entity);

    @Update
    void updateEntity(MyEntity entity);
}

Usage:

long result = dao.insertOrIgnore(entity);
if (result == -1) {
    // Row existed – perform a partial update if needed
    dao.updateEntity(entity);
}

Missing Diagnostic Detail

To fine‑tune this pattern, could you confirm whether the entity’s primary key is autogenerated or supplied manually? The conflict behavior and return value interpretation differ slightly between these two scenarios.