如何避免房间外键错误-约束失败(代码787) [英] How to avoid room foreign key error - constraint failed (code 787)

问题描述

我有3个实体-1个孩子和2个父母. 2个父实体可能有很多子实体，每个人都有自己的子代.

I have 3 entities - 1 child and 2 parent. 2 parent entity may have many child entities, everyone has their own.

这是孩子:

@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可以具有多个不相交的共享，它们各自具有自己的唯一性.

Pool and Note can have several Share, which do not intersect, each of them has its own and unique.

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

But when i try to save Share i have next error:

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:

It seems that you are trying to put two foreign-key constraints on the same column (entityId). Bizarrely, SQLite will allow you create a table with this setup. However, when you add a new row it will check its foreign key constraints to verify that the value exists in the other table(s). So in order for this to succeed, you need to have the entityId in both tables:

Pool
1|pool1
2|pool2

Note 
1|note1

如果我创建一个具有entityId = 1的新共享，这将成功，因为我有一个id = 1的池和一个id = 1的便笺.

If I create a new share with entityId = 1 this will succeed because I have a pool with id=1 and a note with id=1.

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

But if I try to create a share with entityId = 2, foreign constraint validation will fail because there is no note with id=2.

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

You need to rethink the structure of your tables so that you don't have multiple foreign keys on the same column, possibly with a linking table.

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

You can test this in 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

这篇关于如何避免房间外键错误-约束失败(代码787)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！