Issue

This Content is from Stack Overflow. Question asked by Baris evik

Solution

The error is saying that you are trying to insert a row that already has the same value for the uuuid, which being the primary key must be a unique value.

The options are

• to supply a unique uuuid value for each Model you are inserting
• leave the uuuid as 0 and let the uuuid value be generated
• use @Insert(onConflict = onConflictStrategy.IGNORE)
  • in which case the row will not be inserted and the Long in the List returned that that row will be -1.
• user @Insert(onConflict = OnConflictStrategy.REPLACE)
  • in which case the row being inserted will replace the previously inserted row that has the same uuuid.
• implement a Try/Catch block and handle the conflict accordingly.

Demo

Perhaps consider this demo based upon the code you have shown.

• note that for brevity and convenience the main thread has been used.

The Model class (table):-

@Entity
data class Model(
    @PrimaryKey(autoGenerate = true)
    var uuuid: Int=0,
    var tittle: String
)

The @Dao annotated interface with 3 inserts for the options above (i.e. the 3 permutations of onConflict)

@Dao
interface Roomdao {
    @Insert
    fun insertt1(vararg model: Model): List<Long>
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insertt2(vararg model: Model): List<Long>
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertt3(vararg model: Model): List<Long>
}

Now some code in an Activity. That will insert 3 rows at a time using the each of the 3 inserts (so 9 rows) with 3 combinations of specifying the uuuid in 3 ways (so 27 attempts to insert a row)

1. not al all so the default 0 is used
2. as 0
3. as a non zero positive value

:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: Roomdao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getRoomdao()

        resultsLog(dao.insertt1(Model(tittle = "Title1"), Model(tittle = "Title2"), Model(tittle = "Title3")),"01")
        resultsLog(dao.insertt2(Model(tittle = "Title1"), Model(tittle = "Title2"), Model(tittle = "Title3")),"02")
        resultsLog(dao.insertt3(Model(tittle = "Title1"), Model(tittle = "Title2"), Model(tittle = "Title3")),"03")
        insertInTryCatch( 0,1,"04")
        insertInTryCatch(0,2,"05")
        insertInTryCatch(0,3,"06")
        insertInTryCatch(100,1,"07")
        insertInTryCatch(110,2,"08")
        insertInTryCatch(120,3,"09")
    }

    fun insertInTryCatch(uuuid: Int, insertType: Int, suffix: String) {
        try {
            when(insertType) {
                1 -> resultsLog(dao.insertt1(Model(uuuid,"Title1"), Model(uuuid,"Title2"),Model(uuuid,"Title3")),suffix)
                2 -> resultsLog(dao.insertt2(Model(uuuid,"Title1"), Model(uuuid,"Title2"),Model(uuuid,"Title3")),suffix)
                3 -> resultsLog(dao.insertt3(Model(uuuid,"Title1"), Model(uuuid,"Title2"),Model(uuuid,"Title3")),suffix)
                else -> Log.d("DBINFOOOOPS","$insertType is not a valid insertYType")
            }
        } catch (e: SQLiteException) {
            Log.d("DBINFOERROR_$suffix",e.message!!.toString())
        }
    }
    fun resultsLog(result: List<Long>, suffix: String) {
        var okCount = 0;
        var failCount = 0
        var sb = StringBuilder()
        for (l in result) {
            if (l < 1) failCount++ else okCount++
            if (sb.length > 0) sb.append(",")
            sb.append(l)
        }
        Log.d("INSERTINFO_$suffix","Inserted $okCount rows, failed to insert $failCount rows.\n\treturn values (ID's) being $sb")

    }
}

Result of the Demo

The result is that there first 18 rows are inserted as the uuuid is generated because it’s value is 0.

However, of the last set of 9, the results according to the List returned indicates that 4 rows were inserted as per the Log:-

2022-09-18 09:31:36.914 D/INSERTINFO_01: Inserted 3 rows, failed to insert 0 rows.
        return values (ID's) being 1,2,3
2022-09-18 09:31:36.917 D/INSERTINFO_02: Inserted 3 rows, failed to insert 0 rows.
        return values (ID's) being 4,5,6
2022-09-18 09:31:36.935 D/INSERTINFO_03: Inserted 3 rows, failed to insert 0 rows.
        return values (ID's) being 7,8,9
        
2022-09-18 09:31:36.942 D/INSERTINFO_04: Inserted 3 rows, failed to insert 0 rows.
        return values (ID's) being 10,11,12
2022-09-18 09:31:36.946 D/INSERTINFO_05: Inserted 3 rows, failed to insert 0 rows.
        return values (ID's) being 13,14,15
2022-09-18 09:31:36.950 D/INSERTINFO_06: Inserted 3 rows, failed to insert 0 rows.
        return values (ID's) being 16,17,18
        
2022-09-18 09:31:36.953 D/DBINFOERROR_07: UNIQUE constraint failed: Model.uuuid (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)
2022-09-18 09:31:36.956 D/INSERTINFO_08: Inserted 1 rows, failed to insert 2 rows.
        return values (ID's) being 110,-1,-1
2022-09-18 09:31:36.959 D/INSERTINFO_09: Inserted 3 rows, failed to insert 0 rows.
        return values (ID's) being 120,120,120

However, inspection of the database shows only 20 rows, rather than the suggested 22 from the attempt to add 27 rows:-

To explain:-

1. as insertt1 uses no defined onConflict the default ABORT is applied. As the generated code does the inserts within a transaction and a failure occurs the changes are rolled back (undone) so no rows are inserted.
2. as insertt2 uses IGNORE the duplicates will be ignored so 1 row is inserted (the 19th row).
3. as insertt3 uses REPLACE the duplicates will replace the duplicated row so although all 3 are inserted, the 1st is a new row, the 2nd and 3rd just update the 1st row.

 This Question was asked in  StackOverflow by  Baris evik and Answered by MikeT It is licensed under the terms of 
CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.