Android Room FOREIGN KEY constraint failed (code 787)

Question

I'm tying to create a database in Android Room with two foreign keys. Every time I try to insert a track into the database the program chrashes and says that the "foreign key costraint failed(code 787)". Maybe someone of you knows why and can help me.

@Entity(foreignKeys = {@ForeignKey(
    entity = Kategorie.class,
    childColumns = "kategorieFremdschluessel",
    parentColumns = "kategorieID",
    onUpdate = ForeignKey.CASCADE,
    onDelete = ForeignKey.CASCADE
    ),
    @ForeignKey(
            entity = Playlist.class,
            childColumns = "playlistFremdschluessel",
            parentColumns = "uuid",
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE
    )
 })

public class Track {

@PrimaryKey(autoGenerate = true)
private int uid;

private String trackTitel;
private String playlistName;
private String jsonObjectString;
private int kategorieFremdschluessel;
private int playlistFremdschluessel;

@Ignore
public Track(String trackTitel, String playlistName, String jsonObjectString) {
    this.trackTitel = trackTitel;
    this.playlistName = playlistName;
    this.jsonObjectString = jsonObjectString;
}

public Track(String trackTitel, String jsonObjectString) {
    this.trackTitel = trackTitel;
    this.jsonObjectString = jsonObjectString;
}
//Getter and Setter

@Dao

TrackDao

public interface TrackDao {

@Query("SELECT * FROM Track WHERE playlistName LIKE :playlist")
List<Track> getAllTracks(String playlist);

@Query("SELECT * FROM Track WHERE kategorieFremdschluessel = :kategorieFremdschluessel")
List<Track> loadAllKategorieTracks(int kategorieFremdschluessel);

@Query("SELECT * FROM Track WHERE playlistFremdschluessel = :playlistFremdschluessel")
List<Track> loadAllPlaylistTracks(int playlistFremdschluessel);

@Insert
void insertAll(List<Track> trackList);

@Insert
void insertOne(Track track);

@Update
void updateOne(Track track);

@Delete
void delete(Track track);
}

"Kategorie" and "Playlist" are also tables in the database.

@Entity
public class Playlist{


@PrimaryKey(autoGenerate = true)
private int uuid;

@ColumnInfo(name = "name")
private String name;

Kategorie

@Entity
public class Kategorie {



@PrimaryKey(autoGenerate = true)
private int kategorieID;


@ColumnInfo(name = "name")
private String name;

Do you have rows in Kategorie and Playlist tables with uid that is same as uid of track that you are inserting? — Vladimír Bielený, Jun 06 '18 at 21:09
@ElectroWeak Yes, both classes have a PrimaryKey. Kategorie = "kategorieID" and Playlist = "uuid" . So the names are identical. — Felix, Jun 06 '18 at 21:12
No, I mean that you need to have a row in table Kategorie and Playlist with the same primary key, which is also same as the primary key of Track you are inserting. — Vladimír Bielený, Jun 06 '18 at 21:17
In other words, you can't insert Track into a database if there isn't a corresponding row in Kategorie and Playlist with the same foreign key. More info about foreign keys [here](https://www.sqlite.org/foreignkeys.html) — Vladimír Bielený, Jun 06 '18 at 21:25
Oh okay...wow that was the problem. I'm feeling stupid because I tested everything... Okay another question then, is there a possiblity to say, that the foreign key can be null first? And when I want I can set it to the correct ID? — Felix, Jun 06 '18 at 21:37
Yes, if you set Track's foreign key as null, you can add Track and then update it with ID, but you'd need to change `kategorieFremdschluessel` from type `int` to `Integer` because otherwise, Room generates column as NOT NULL which forbids null values in a table. — Vladimír Bielený, Jun 06 '18 at 21:50
Wonderful! You are the best! I would give you a like if I could :D Thank you so much! — Felix, Jun 06 '18 at 21:54
Do it! Someone will benefit from it because i searched for that answer a long time and didn't find anything. — Felix, Jun 06 '18 at 21:56

score 4 · Answer 1 · answered Apr 26 '19 at 00:22

There's another reason why this error occurs which consumed a lot of my time since every possible solution available on the internet seemed futile in my case.

If you use rowId of the parent table as the parentColumn key, the Foreign key constraint CANNOT be used.

As per the SQLite documentation on Foreign Keys:

The parent key is the column or set of columns in the parent table that the foreign key constraint refers to. This is normally, but not always, the primary key of the parent table. The parent key must be a named column or columns in the parent table, not the rowid.

Also have a look at my question https://stackoverflow.com/questions/76840478/sqliteconstraintexception-foreign-key-constraint-failed-code-787 — Taimoor Khan, Aug 05 '23 at 07:33

score 3 · Accepted Answer · answered Jun 06 '18 at 21:58

3

You need to have rows in tables Kategorie and Playlist with a same foreign key as has Track that you are inserting.

answered Jun 06 '18 at 21:58

Vladimír Bielený

2,795
2
11
16

2

@jlopez If you are getting the same error then check if there are rows in the table in which you want to insert the row. There must be a row with the same foreign key as the one in the row you want to insert. – Vladimír Bielený Jul 03 '18 at 17:57
Also have a look at my question https://stackoverflow.com/questions/76840478/sqliteconstraintexception-foreign-key-constraint-failed-code-787 – Taimoor Khan Aug 05 '23 at 07:34

Android Room FOREIGN KEY constraint failed (code 787)

2 Answers2

Linked