Are Content Provider Column id's always unique no matter the case?

Question

e.g

MediaStore.Audio.Media._ID.

The reason why Im asking is because Im reading music data and writing them into Room database. I dont want there to be a situation where a song cant be added because it's using an ID stored in my Room database.

This is how I write the data to Room. I have to use this conflict strategy or else it keeps adding back deleted songs.

@Transaction
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun addSongs(song: SongTrack)

I have this query that removes everything from a SongTrack entity apart from the ID/ DbId(ID/ DbId is just to log deleted songs).

@Query("UPDATE songtrack SET albumUri = null, 
 songTitle = '', albumTitle= '' " + ", genre = '', image = '' WHERE id = 
:songId" )
fun removeBySongId(songId: Int)

Heres the entity:

@Entity
data class SongTrack(

@PrimaryKey(autoGenerate = false)
var dbId: Int,
val id: Int,
val songTitle: String,
val artistName: String,
val albumUri: String?,
val image: String?,
val duration: Long,
val albumTitle: String,
val genre: String)

                  

Hopefully this all makes sense. If Column Ids are forever unique even if someone has deleted that data on their phone my approach is safe.

Otherwise, how can I prevent room from adding back data I removed from Room database?

Answer 1

Just naming a column id does not make it a UNIQUE column. What makes a column UNIQUE is having a UNIQUE index on the column.

In your case the dbId column/field has an implicit UNQIUE index as it is defined as the PrimaryKey. So the dbId column MUST be a UNIQUE value.

• If an attempt is made to add a row where the value of the dbId already exists in another row then a UNIQUE conflict will occur and as you have found you can negate the failure by using INSERT OR IGNORE i.e. the onConflictStrategy of IGNORE.

As such your code will prevent another row being added if the dbId value already exists in the table.

The question says:-

I have this query that removes everything from a SongTrack entity apart from the ID/ DbId(ID/ DbId is just to log deleted songs).

However, the id column/field does not have a UNIQUE index and thus any row can have any value in the id column/field; only if the dbId column/field is not a unique value would the row not be inserted due to the ignored unique conflict.

If you wanted the id column/field to also be unique then you cannot have multiple primary keys (although you can have a primary key (a composite primary key) that consists of multiple columns but then it is the combination of the columns that is UNIQUE not an individual column).

Room does not cater, via annotation of columns/fields, for a composite primary key nor for UNIQUE indexes. You have to use the primaryKeys and/or indices parameter of the @Entity annotation to define these indexes.

If you want the id column to also be UNIQUE (i.e. as well as the dbId column) then you would have to use the indices parameter e.g.

@Entity(indices = [Index("id", unique = true)])

With the above added then dbId would have to be UNIQUE and also id would have to be UNIQUE and a conflict of either or both would result in the insert being ignored.