如何配置Android Room关联表外键以支持零值输入

问题原因分析 #

• 核心原因1：SQLite外键约束仅允许两种合法值：要么对应父表中存在的主键值，要么为NULL。你当前将可选外键字段设为非空int类型，默认值0，而父表中不存在ID为0的记录，自然触发约束报错。
• 核心原因2：桌面版SQLite默认关闭外键约束校验，所以即使值不匹配也不会抛出异常，而Android Room默认开启外键约束，这就是两端表现不一致的根源。
• OnConflict注解仅用于处理主键、唯一约束、CHECK约束的冲突场景，无法解决外键约束报错问题。

最优解决方案（符合SQL规范） #

将3个可选关联的外键字段从基本类型int改为包装类Integer，允许为空，用NULL表示无关联数据（外键约束会自动忽略NULL值，不会触发校验），修改后的实体类代码如下：

@Entity(tableName = "Notes", foreignKeys = {
        @ForeignKey(entity = Sources.class, parentColumns = "SourceID", childColumns = "SourceID"),
        @ForeignKey(entity = Comments.class, parentColumns = "CommentID", childColumns = "CommentID"),
        @ForeignKey(entity = Questions.class, parentColumns = "QuestionID", childColumns = "QuestionID"),
        @ForeignKey(entity = Quotes.class, parentColumns = "QuoteID", childColumns = "QuoteID"),
        @ForeignKey(entity = Terms.class, parentColumns = "TermID", childColumns = "TermID"),
        @ForeignKey(entity = Topics.class, parentColumns = "TopicID", childColumns = "TopicID")},
        indices = {@Index("SourceID"), @Index("CommentID"), @Index("QuestionID"), @Index("QuoteID"),
                @Index("TermID"), @Index("TopicID")})
public class Notes {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "NoteID")
    private int noteID;
    @ColumnInfo(name = "SourceID")
    private int sourceID;
    @ColumnInfo(name = "CommentID")
    private int commentID;
    // 改为Integer允许为空，默认值设为NULL
    @ColumnInfo(name = "QuestionID", defaultValue = "NULL")
    private Integer questionID;
    @ColumnInfo(name = "QuoteID", defaultValue = "NULL")
    private Integer quoteID;
    @ColumnInfo(name = "TermID", defaultValue = "NULL")
    private Integer termID;
    @ColumnInfo(name = "TopicID")
    private int topicID;
    @ColumnInfo(name = "Deleted", defaultValue = "0")
    private int deleted;

    public Notes(int noteID, int sourceID, int commentID, Integer questionID, Integer quoteID, Integer termID, int topicID, int deleted){
        this.noteID = noteID;
        this.sourceID = sourceID;
        this.commentID = commentID;
        this.questionID = questionID;
        this.quoteID = quoteID;
        this.termID = termID;
        this.topicID = topicID;
        this.deleted = deleted;
    }
    // 对应getter、setter也要同步修改参数/返回值类型为Integer
}

修改后需要升级数据库版本，做好迁移：将对应字段的NOT NULL约束改为允许空，默认值改为NULL即可。

备用解决方案（适配原有0值逻辑） #

如果必须保留0作为无关联的标识，可在Questions、Quotes、Terms三张父表中预先插入一条ID为0的占位记录，这样0作为合法的父表主键值，插入时就不会触发外键约束报错，该方案属于兼容适配方案，不如第一种方案规范。

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