How to ensure unique constraint over multiple columns in Room, even if one of them is null?

Question

I have a Room database in my application with one table containing received and sent messages. Inside of the table, the messages are just differentiated by the phone number, being null for the backend-server (since a server has no phone number) and the phone number of the user for the sent messages. (Entered on app installation, just as Whatsapp.) To sync the table with the backend, I introduced a new column, containing the backend id of the messages on the server. Since the server seperates sent and received messages (due to different information contained in the tables backend), the id of a sent message and the id of a received message can be equal, only distinguishable by the corresponding phone number. (Either null or own) So I created a unique constraint over both columns: backend_id & phone number.

@Entity(indices = [Index(value = ["backend_id", "senderNumber"], unique = true)])
data class Message(
        var senderNumber: String?,
        var message: String?,
        var backend_id: String? = null,
        var time : Date? = Date(),
        var status : Status = Status.PENDING
) : ListItem(time), Serializable {
    @PrimaryKey(autoGenerate = true) var id : Long? = null
}

But trying it out with some messages, I had to realize, that the database gladly accepts equal backend_ids, if the phone number is null. To make sure this was not an accident, I even wrote a UnitTest:

@RunWith(AndroidJUnit4::class)
class ExampleInstrumentedTest {
    lateinit var db : MyDatabase
    lateinit var dao : MessageDao
    @Before
    fun createDb() {
        val context = ApplicationProvider.getApplicationContext<Context>()
        db = Room.inMemoryDatabaseBuilder(
                context, MyDatabase::class.java).build()
        dao = db.messageDao()
    }
    @After
    @Throws(IOException::class)
    fun closeDb() {
        db.close()
    }

    @Test(expected = Exception::class)
    fun check_unique_constraint_is_violated() {
        // Context of the app under test.
        val message = Message(senderNumber = null, backend_id = "1", time = Date(), message = "Hello")
        dao.insertAll(message)
        dao.insertAll(message)
        val allMessages = dao.getAll()
        assertTrue(allMessages.size==2)
        assertTrue(allMessages[0].backend_id==allMessages[1].backend_id)
    }
}

This test fails, since it doesn´t throw any exception. Debugging it shows, that the Room database also doesn´t catch the exception silently, since both messages (being the same) are being inserted successfully, resulting in 2 messages.

So my question is: How can I ensure, that the result is unique over both columns, even if one of them is null? It seems a bit weird to me, that you can pass-by uniqueness, just by inserting null for one of the columns. It worked, when I only checked the backend_id in the index, throwing exceptions, when a sent and a received message had the same id. (But I obviously don´t want that.)

In case Database and Dao have any relevance to the solution: Database:

@Database(entities = [Message::class], version = 1)
@TypeConverters(Converters::class)
abstract class MyDatabase : RoomDatabase() {
    override fun init(configuration: DatabaseConfiguration) {
        super.init(configuration)
        //Create and execute some trigger, limiting the entries on the latest 50
    }
    abstract fun messageDao() : MessageDao
    companion object {
        private var db: MyDatabase? = null
        private fun create(context : Context) : MyDatabase {
            return Room.databaseBuilder(context, MyDatabase::class.java, "dbname").build()
        }
        fun getDB(context : Context) : MyDatabase {
            synchronized(this) {
                if(db==null) {
                    db = create(context)
                }
                return db!!
            }
        }
    }
}

MessageDao:

@Dao
interface MessageDao {
    @Query("SELECT * FROM Message")
    fun getAll() : List<Message>

    @Insert
    fun insertAll(vararg messages: Message) : List<Long>
}

Answer 1

In SQLite (and others that conform to SQL-92) null is considered different to any other null and hence your issue.

As such you should not be using null. You can overcome this setting the default value to a specific value that indicates a no supplied value.

For example you could use:-

@NotNull
var backend_id: String = "0000000000"

• 0000000000 could be any value that suits your purpose.
  • "" could also be used.

---

---

Altenative An alternative approach could be to handle the null in the index such as :-

@Entity(indices = [Index(value = ["coalesce(backend_id,'00000000')", "senderNumber"], unique = true)])

HOWEVER, Room will issue an error message because it doesn't determine that the backend_id column is the column being indexed and thus issues a compilation error e.g. :-

error: coalesce(backend_id,'00000000') referenced in the index does not exists in the Entity.

Therefore you would need to add the index outside of Room's creation of tables. You could do this via the onCreate or onOpen callback. Noting that onCreate is only called once when the database is first created and that onOpen is called every time the app is run.

The safest (data wise) but slightly less efficient is to use the onOpen callback.

Here's an example that creates the index (applying it to both columns, considering that both backend_id and senderNumber columns can be null).

This being done when building the database :- ....

                .addCallback(object :RoomDatabase.Callback() {
                    override fun onOpen(db: SupportSQLiteDatabase) {
                        super.onOpen(db)
                        db.execSQL(
                            "CREATE UNIQUE INDEX IF NOT EXISTS message_unique_sn_beid " +
                                    "ON message (coalesce(backend_id,''),coalesce(senderNumber,''));")
                    }
                    override fun onCreate(db: SupportSQLiteDatabase) {
                        super.onCreate(db)
                    }

                })
                .build()
                ....

• The index name would be message_unique_sn_beid

Results using the Alternative

Basing the Message Entity on your (but with fewer columns) and an Insert Dao of :-

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(message: Message): Long

using the following (and with the index added via the onOpen callback) the when running :-

    dao.insert(Message(null,"1234567890","blah"))
    dao.insert(Message(null,"0123456789","blah","0123456789"))
    dao.insert(Message(null,"1234567890","blah"))
    dao.insert(Message(null,"1234567890","blah",null))
    dao.insert(Message(null,null,message = "blah",backend_id = "9876543210"))
    dao.insert(Message(null,null,message = "blah",backend_id = "9876543210"))

• 1st and 2nd rows will be added, 3rd and 4th rows will be ignored due to UNIQUE conflict 5th (3rd row in table) will be added, 6th will be ignored due to UNIQUE conflict.

Using Android Studio's Database Inspector:-

1. The message table :-

2. Looking at the sqlite_master (the schema) at items starting with mess (i.e. running SQL SELECT * FROM sqlite_master WHERE name LIKE 'mess%';) :-