How to insert a duplicate row in Room Db?

Question

While searching for this, I only came across people asking how to Avoid inserting duplicate rows using room db. But my app has a feature where the user may tap a copy button and the list item will get inserted again in the db. I could have simply achieved this if my table didn't have a primary key set on one of its fields. While I found this solution for SQLite, I don't know how I can achieve this in Room Db. Because while writing an insert query with custom queries in room would defeat the purpose of using room in the first place.

You can achieve this by removing the primary key or creating a composite primary key. — aleem md, May 06 '20 at 09:21
Have you checked this : https://developer.android.com/reference/androidx/room/OnConflictStrategy. You can simply set the onConflict strategy to replace in your dao insert method. — Tayyab Mazhar, May 06 '20 at 09:22
@TayyabMazhar I dont want to replace new data, I want a new row — Aman Grover, May 06 '20 at 09:54
So, you want a new row with same data and a new primary key? — Tayyab Mazhar, May 06 '20 at 09:55

score 2 · Answer 1 · answered May 06 '20 at 11:54

2

Let's say you have some entity

@Entity(tableName = "foo_table")
data class Foo (
    @PrimaryKey(autoGenerate = true) var id: Int,
    // or without autogeneration
    // @PrimaryKey var id: Int = 0,
    var bar:String
)

and you have some Dao with insert:

@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insert(foo: Foo)

Then to copy your existing value (copiedValue: Foo) you need in some way to manage your primary key:

Scenario 1. Your Primary Key is autogenerated, you have to set it to default value to get new autogenerated one:

copiedValue.id = 0 yourDao.insert(copiedValue)
Scenario 2. Your Primary Key is not autogenerated, you have to set new primary key manually:

copiedValue.id = ... // some code to set new unique id yourDao.insert(copiedValue)

answered May 06 '20 at 11:54

sergiy tikhonov

4,961
1
10
27

right now I am using the first solution you gave, copying the object and inserting it after setting it's primary key value to default 0. But it feels like this should be implemented with sql query only. I found a solution with sql [here](https://stackoverflow.com/questions/9156340/how-to-copy-a-row-and-insert-in-same-table-with-a-autoincrement-field-in-mysql), the answer where they create a temporary table, but room considers this 3 separate queries and there's no way to chain more than 1 query with room. – Aman Grover May 07 '20 at 15:00
Honestly, I can't see the benefit in implementing in Room method described in that article. Room is actually ORM and using operations with the objects instead of using sql-queries is natural behaviour for ORMs. However, is seemed you can try to implement method described in answer ACCEPTED in article you've mentioned (for example in addition to existing "insert-method" in your DAO you can add some "copy-method" with id-parameter) – sergiy tikhonov May 07 '20 at 16:45

How to insert a duplicate row in Room Db?

1 Answers1