I'm trying to do an upsert with Android Room's @Upsert annotation, but I'm unsure what I'm doing incorrectly because when it's returning me the exception:

android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: coin_status.coin_query_id (code 2067 SQLITE_CONSTRAINT_UNIQUE)

My Dao object with the upsert annotation is:

@Dao
public interface CoinStatusDao {
    @Upsert
    Single<List<Long>> updateFavorites(List<CoinStatus> favoriteCoins);
}

With the CoinStatus object:

@Entity(tableName = "coin_status", indices = {@Index(value = {"coin_query_id"}, unique = true)})
public class CoinStatus {
    @PrimaryKey(autoGenerate = true)
    private int primaryKey;

    @ColumnInfo(name = "coin_query_id")
    private String coinQueryId;

    @ColumnInfo(name = "is_favorite")
    private boolean isFavorite;

    public CoinStatus(String coinQueryId, boolean isFavorite) {
        this.coinQueryId = coinQueryId;
        this.isFavorite = isFavorite;
    }

    ...
    ...
    //getters and setters

}

I am able to successfully do inserts and querys with no problem

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    Single<List<Long>> insertFavorite(List<CoinStatus> coinStatuses);

    @Query("SELECT * FROM coin_status ORDER BY coin_query_id ASC")
    Single<List<CoinStatus>> getFavoriteCoins();

But I'm not sure what I'm missing with the Upsert.

What am I doing incorrectly here?

CodePudding user response：

It is because you have two UNIQUE constraints, the Primary Key and the index on the coinQuery.

If you drop the primaryKey member variable then the UPSERT will work fine.

e.g. :-

@Entity(tableName = "coin_status"/*, indices = {@Index(value = {"coin_query_id"}, unique = true)}*/)
public class CoinStatus {

   //@PrimaryKey(autoGenerate = true)
   //private int primaryKey;

   @PrimaryKey
   @NotNull
   @ColumnInfo(name = "coin_query_id")
   private String coinQueryId;

   @ColumnInfo(name = "is_favorite")
   private boolean isFavorite;

   public CoinStatus(String coinQueryId, boolean isFavorite) {
      this.coinQueryId = coinQueryId;
      this.isFavorite = isFavorite;
   }

   /*
   public int getPrimaryKey() {
      return primaryKey;
   }
    */

   public String getCoinQueryId() {
      return coinQueryId;
   }

   public boolean isFavorite() {
      return isFavorite;
   }

   /*
   public void setPrimaryKey(int primaryKey) {
      this.primaryKey = primaryKey;
   }

    */

   public void setCoinQueryId(String coinQueryId) {
      this.coinQueryId = coinQueryId;
   }

   public void setFavorite(boolean favorite) {
      isFavorite = favorite;
   }
}

• note that rather than excluding code, the excluded code has been commented out

With the above and :-

public class MainActivity extends AppCompatActivity {

    private static final String TAG = "DBINFO";
    TheDatabase db;
    CoinStatusDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = TheDatabase.getInstance(this);
        dao = db.getCoinStatusDao();

        try {
            dao.updateFavorites(Arrays.asList(
                    new CoinStatus("Penny", true), 
                    new CoinStatus("Cent", false)
            ));
            logCurrentCoinStatusRow("_RUN1");
            dao.updateFavorites(Arrays.asList(
                    new CoinStatus("Penny", false), 
                    new CoinStatus("Cent", true), 
                    new CoinStatus("Shilling",true)
            ));
            logCurrentCoinStatusRow("_RUN2");
        }
        catch (SQLiteException e) {
            e.printStackTrace();
        }
    }

    private void logCurrentCoinStatusRow(String tagSuffix) {
        for(CoinStatus c: dao.getFavoriteCoins()) {
            Log.d(TAG   tagSuffix," CoinQueryID = "   c.getCoinQueryId()   " IsFavourite is "   c.isFavorite());
        }
    }
}

The the log includes:-

D/DBINFO_RUN1:  CoinQueryID = Cent IsFavourite is false
D/DBINFO_RUN1:  CoinQueryID = Penny IsFavourite is true

D/DBINFO_RUN2:  CoinQueryID = Cent IsFavourite is true
D/DBINFO_RUN2:  CoinQueryID = Penny IsFavourite is false
D/DBINFO_RUN2:  CoinQueryID = Shilling IsFavourite is true

i.e. the two non-existent rows have been inserted as expected and in the 2nd run the Cent and Penny have been updated (flipped the IsFavourite value) and the Shilling has been added.

Without modifying the CointStatus class

The alternative would be to keep the dual UNQIUE indexes and thus no changes to CoinStatus but to instead ensure that existing CoinStatus's have all values set (so as to not try to generate the primaryKey).

• This would be the more awkward solution and very likely less efficient as you instead of need to just know the coinQueryId you have to also get the associated primaryKey which would likely mean getting it/them from the table.

So using the original CoinStatus then the second RUN will work if the code is changed, as an example, to:-

        dao.updateFavorites(Arrays.asList(
                new CoinStatus("Penny", true),
                new CoinStatus("Cent", false)
        ));
        logCurrentCoinStatusRow("_RUN1");

        List<CoinStatus> coins = dao.getFavoriteCoins();
        for (CoinStatus c: coins) {
            c.setFavorite(!c.isFavorite());
        }
        coins.add(new CoinStatus("Shilling",true));

        dao.updateFavorites(coins);
        logCurrentCoinStatusRow("_RUN2");

i.e. the primaryKey values are being obtained and then the isFavorite value is being switched, then the new Shilling is being added. The results being identical:-

D/DBINFO_RUN1:  CoinQueryID = Cent IsFavourite is false
D/DBINFO_RUN1:  CoinQueryID = Penny IsFavourite is true

D/DBINFO_RUN2:  CoinQueryID = Cent IsFavourite is true
D/DBINFO_RUN2:  CoinQueryID = Penny IsFavourite is false
D/DBINFO_RUN2:  CoinQueryID = Shilling IsFavourite is true