How to avoid room foreign key error - constraint failed (code 787)

Question

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

This is child:

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

And this is parents:

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

How to avoid this error?

Answer 1

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

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.

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.

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

Answer 2

One field cannot reference two foreign keys. In your setup, you are declaring that "entity_id" is foreign key of type Pool, which parent column is Pool.id AND "entity_id" is foreign key of type Note, which parent column is Note.id. This is an invalid constraint.

You will need to add a new column in the Share table that will reference the Note table as a foreign key. Add new field, i.e. "note_id" of type String and annotate it as a foreign key to the Note class. Something like this:

@Entity(
    tableName = "share",
    foreignKeys = [
        ForeignKey(
                entity = Pool::class,
                childColumns = ["entity_id"],
                parentColumns = ["id"],
                onDelete = CASCADE
        ),
        ForeignKey(
                entity = Note::class,
                childColumns = ["note_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,

    @ColumnInfo(name = "note_id")
    val noteId: String
)

I am not sure about the structure of your database, but I don't know the idea behind the app and I cannot comment about the structure. I can give you one tip, though: if possible, use integers instead of Strings for primary keys - it makes database operations a lot faster.

I hope this answer helps you :)