How To Avoid Room Foreign Key Error - Constraint Failed (code 787)

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', foreign

Solution 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;

CREATETABLE pool (id INTEGERPRIMARY KEY, name TEXT);
CREATETABLE note (id INTEGERPRIMARY KEY, name TEXT);
CREATETABLE share (id INTEGERPRIMARY KEY, entityId INTEGER, FOREIGN KEY(entityId) REFERENCES pool(id), FOREIGN KEY(entityId) REFERENCES note(id));

insertinto pool (name) values ('pool1');
insertinto pool (name) values ('pool2');
insertinto note (name) values ('note1');

select*from pool;
1|pool1
2|pool2

select*from note;
1|note1

insertinto share (entityId) values (1);

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

Solution 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 :)