Room如何实现多列联合唯一约束，即使其中一列值为null？

问题根源 #

该问题是SQLite的固有特性导致的：SQLite中NULL值不参与唯一性约束校验，任意两个NULL值都会被判定为不相等。所以你给backend_id和senderNumber加的联合唯一索引，只要其中senderNumber为NULL，不管backend_id是不是重复，都不会触发冲突。 #

可行解决方案 #

方案1：替换NULL为特殊固定值（最推荐，实现成本最低、可靠性最高） #

不要给服务器下发的消息senderNumber存NULL，统一用一个不会和普通用户手机号重复的特殊字符串作为标记，比如"_SERVER_SENDER_"：

1. 修改实体类，将senderNumber改为非空类型，服务器下发消息赋值时统一填特殊标记值：

// 定义全局常量
const val SERVER_SENDER_TAG = "_SERVER_SENDER_"

@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
}

2. 新增数据库迁移逻辑，把旧表里所有senderNumber为NULL的记录批量更新为SERVER_SENDER_TAG，升级数据库版本即可。
   这个方案完全复用了原生的联合唯一约束，不需要额外逻辑，性能和可靠性都是最高的。

方案2：新增SQLite触发器实现约束 #

如果不想改动现有senderNumber存NULL的逻辑，可以通过数据库触发器在插入/更新前做校验：
在你的MyDatabase的init方法中执行触发器创建语句：

override fun init(configuration: DatabaseConfiguration) {
    super.init(configuration)
    // 创建唯一性校验触发器
    openHelper.writableDatabase.execSQL("""
        CREATE TRIGGER IF NOT EXISTS check_unique_backend_id_sender
        BEFORE INSERT ON Message
        FOR EACH ROW
        BEGIN
            SELECT RAISE(ABORT, 'Duplicate message')
            WHERE EXISTS (
                SELECT 1 FROM Message 
                WHERE backend_id = NEW.backend_id 
                AND (senderNumber = NEW.senderNumber OR (senderNumber IS NULL AND NEW.senderNumber IS NULL))
            );
        END;
    """.trimIndent())
    // 原有其他逻辑，比如限制最多存50条的触发器
}

这个触发器会在插入前检查是否存在符合重复条件的记录，存在就直接抛出异常终止插入，符合你的业务要求。 #

方案3：Dao层插入前自行校验（不推荐，存在并发风险） #

在Dao中新增查询方法，插入前先判断是否存在重复记录：

@Dao
interface MessageDao {
    // 新增查询重复记录的方法
    @Query("""
        SELECT COUNT(*) FROM Message 
        WHERE backend_id = :backendId 
        AND (senderNumber = :senderNumber OR (senderNumber IS NULL AND :senderNumber IS NULL))
    """)
    suspend fun countDuplicate(backendId: String, senderNumber: String?): Int

    // 原有其他方法
    @Query("SELECT * FROM Message")
    fun getAll() : List<Message>

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

插入前先调用countDuplicate判断是否有重复，有则走冲突处理逻辑，没有再执行插入。缺点是如果并发插入同一条记录，可能两次查询都判断无重复，最终还是插入两条重复数据，可靠性不如数据库层面的约束。 #

内容的提问来源于stack exchange，提问作者Sarah Multitasker