我有3个实体-1个孩子和2个父母。
2个父实体可能有许多子实体,每个人都有自己的。

这是孩子:

@Entity(
        tableName = "share",
        foreignKeys = [
            ForeignKey(
                    entity = Pool::class,
                    childColumns = ["entity_id"],
                    parentColumns = ["id"],
                    onDelete = CASCADE
            ),
            ForeignKey(
                    entity = Note::class,
                    childColumns = ["entity_id"],
                    parentColumns = ["id"],
                    onDelete = CASCADE
            )
        ]
)
data class Share(

        @ColumnInfo(name = "share_id")
        @PrimaryKey(autoGenerate = false)
        val shareId: String,

        @ColumnInfo(name = "entity_id")
        val entityId: String,

        @ColumnInfo(name = "entityType")
        val entityType: Int
)


这是父母:

@Entity(tableName = "pool")
data class Pool(

        @PrimaryKey(autoGenerate = false)
        @ColumnInfo(name = "id")
        val poolId: String,

        @ColumnInfo(name = "category")
        val type: Int
)

@Entity(tableName = "note")
data class Note(

        @PrimaryKey(autoGenerate = false)
        @ColumnInfo(name = "id")
        val noteId: String
)


Pool和Note可以具有多个不相交的共享,它们各自具有自己的唯一性。

但是,当我尝试保存共享时,出现下一个错误:

W/System.err: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)
W/System.err:     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
W/System.err:     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:783)
W/System.err:     at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
W/System.err:     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
W/System.err:     at android.arch.persistence.db.framework.FrameworkSQLiteStatement.executeInsert(FrameworkSQLiteStatement.java:50)
W/System.err:     at android.arch.persistence.room.EntityInsertionAdapter.insertAndReturnIdsList(EntityInsertionAdapter.java:243)
W/System.err:     at com.my_app.data.db.dao.share.ShareDao_Impl.insertShare(ShareDao_Impl.java:114)


如何避免这个错误?

最佳答案

似乎您正在尝试将两个外键约束放在同一列(entityId)上。奇怪的是,SQLite将允许您使用此设置创建表。但是,当您添加新行时,它将检查其外键约束以验证该值是否存在于其他表中。因此,为了使此操作成功,您需要在两个表中都包含entityId:

Pool
1|pool1
2|pool2

Note
1|note1


如果我创建一个带有entityId = 1的新共享,那么它将成功,因为我有一个id = 1的池和一个id = 1的便笺。

但是,如果我尝试创建一个entityId = 2的共享,则外部约束验证将失败,因为没有注释为id = 2。

您需要重新考虑表的结构,以便在同一列上没有多个外键,可能没有链接表。

您可以在SQLite中对此进行测试:

PRAGMA foreign_keys=true;

CREATE TABLE pool (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE note (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE share (id INTEGER PRIMARY KEY, entityId INTEGER, FOREIGN KEY(entityId) REFERENCES pool(id), FOREIGN KEY(entityId) REFERENCES note(id));

insert into pool (name) values ('pool1');
insert into pool (name) values ('pool2');
insert into note (name) values ('note1');

select * from pool;
1|pool1
2|pool2

select * from note;
1|note1

insert into share (entityId) values (1);

insert into share (entityId) values (2);
Error: FOREIGN KEY constraint failed

10-05 17:58