onDelete = NO_ACTION causes error: "SQLiteConstraintException: FOREIGN KEY constraint failed" (code 787)

Question

I am using Room on top of SQLite for Android, and I have the following two Pojo entities:

@Entity
public class Event {
    @PrimaryKey
    @NonNull
    private String eventId;
    private String eventName;
}

@Entity(primaryKeys = {"eventId","attendeeId"},
        foreignKeys = {
                @ForeignKey(entity = Event.class,
                        parentColumns = "eventId",
                        childColumns = "eventId" ,onDelete = NO_ACTION)
        })
public class Attendee {
    @NonNull
    private String eventId;
    @NonNull
    private String attendeeId;
}

When I try to delete a record from the Event table, I get the following error:

Caused by: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787) at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:732)
        at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754)
        at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
        at android.arch.persistence.db.framework.FrameworkSQLiteStatement.executeUpdateDelete(FrameworkSQLiteStatement.java:45)
        at com.minapharm.hixspro.POJO.Room.Events.EventDao_Impl.deleteTable(EventDao_Impl.java:168)

However, if I change the onDelete to CASCADE, no problem occurs!

Am I missing something?

score 2 · Answer 1 · answered Jun 27 '19 at 09:15

Yep. @ForeignKey annotation helps to ensure that an entry can't get into an invalid state (for instance, foreign key can't point to an entry that does not exist).

Imagine you have Event(1, "some event") in your database as well as an attendee Attendee(1, 1). If the event is deleted, than the attendee entry will get into the invalid state, because it's foreign key eventId does not point to a valid entry. That's why you get an exception with description FOREIGN KEY constraint failed.

onDelete = CASCADE ensures that all the dependent entries will be erased with the referenced entry itself: in the described earlier case Attendee(1, 1) will just also get erased from the database when the referenced event entry (Event(1, "some event")) gets deleted.

Deˣ · Accepted Answer · 2019-06-27T09:26:31.540

If you will see here.

By default, NO_ACTION is used.

You don't need to provide it explicitly.

@Entity(primaryKeys = {"eventId","attendeeId"},
        foreignKeys = {
                @ForeignKey(entity = Event.class,
                        parentColumns = "eventId",
                        childColumns = "eventId")
        })
public class Attendee {
    @NonNull
    private String eventId;
    @NonNull
    private String attendeeId;
}

Also, You need to understand the Database is created once. You should reinstall the application and then check whether the delete works or Not.

Edited: As you have defined the ForeignKey but not onDelete CASCADE, You will have to remove all entries manually from Attendee which are pointing to the Event Table row- you are deleting.

onDelete = NO_ACTION causes error: "SQLiteConstraintException: FOREIGN KEY constraint failed" (code 787)

2 Answers2