Android room FOREIGN KEY constraint failed even though values are the same

Question

ROOM database on android refuses to work properly with a foreign key and always errors out no matter what I've tried to do to fix it.

I am trying to implement storing playlists into an Room database. So playlistdb contains the album art, name and id associated with the playlist. Songsdb contains all the information about the song(URI, album art, title, artist and playlist_id(foreign key))

Ignore the messy WIP code but, I have tried to following to fix the problem described in the title:

1. Creating a separate key and not using the parent table's auto-generated primary key

2. hard-coding the values

3. adding log entries to get the values i'm inserting into the database

but to no avail.

the problem lies in the code that inserts the child table in the insertPlaylists function below.

Playlists.java (parent)(part of it)

@Entity(indices = {@Index(value = {"id"}, unique = true) }, tableName = "playlists")
public class Playlists {
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "index")
    public int index;
    @ColumnInfo(name = "id")
    public int id;
    @ColumnInfo(name = "name")
    private String playlistName;
    //this is the associated album art with the playlist, will belong to the first song.
    @ColumnInfo(name = "album_art")
    private String albumArt;

part of Songs.java (child)

@Entity(foreignKeys = {
        @ForeignKey(entity = Playlists.class,
        parentColumns = "id",
        childColumns = "playlist_id",
        onDelete = ForeignKey.CASCADE)},
        tableName = "songs",
        indices = @Index(value = "playlist_id"))
public class Songs {
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "index")
    private int index;
    @ColumnInfo(name = "playlist_id")
    private int playlistID;
    @ColumnInfo(name = "song_id")
    private String songId;
    @ColumnInfo(name = "title")
    private String title;
    @ColumnInfo(name = "album")
    private String album;
    @ColumnInfo(name = "artist")
    private String artist;
    //String can always be converted into URI
    //use String in database for consistency
    @ColumnInfo(name = "album_art")
    private String albumArt;
    @ColumnInfo(name = "duration")
    private String duration;

Function that inserts playlist:

public static void insertPlaylist(Context context, Playlists playlist, SongData[] songs){
        SongsDB songsDB;
        PlaylistsDB playlistsDB;
        songsDB = SongsDB.getInstance(context);
        playlistsDB = PlaylistsDB.getInstance(context);
        int index = 0;
        //insert playlists first
        //get index from playlists so we can increment the value here to give it a unique value.
        index = playlistsDB.playlistsDao().queryLastInsert();
        //increment index
        index++;
        playlist.setPlaylistID(index);
        playlistsDB.playlistsDao().insertPlaylist(playlist);
        //log for sanity check
        Log.e("tesststaAETAST:", Integer.toString(playlistsDB.playlistsDao().queryLastInsert()));
        Log.e("INDEX:", Integer.toString(index));

        //then insert songs
        for(int i =0; i < songs.length; i++)
        {
            Songs song = new Songs(0,songs[i].getSongId(),songs[i].getTitle(),songs[i].getAlbum(),songs[i].getArtist(),songs[i].getAlbumArt().toString(),songs[i].getDuration(), index);
            songsDB.songsDao().insertPlaylist(song);
        }
        SongsDB.destroyInstance();
        PlaylistsDB.destroyInstance();

    }

PlaylistsDao:

@Dao
public interface PlaylistsDao {

    @Query("SELECT * from playlists")
    Cursor queryPlaylists();

    @Query("SELECT id from playlists WHERE name like :name")
    Cursor queryPlaylistID(String name);

    //return 0 if max returns null when table is empty
    @Query("SELECT COALESCE(MAX(id), 0)from playlists")
    int queryLastInsert();

    @Insert()
    void insertPlaylist(Playlists playlists);

    @Update()
    void updatePlaylist(Playlists playlists);

    @Delete()
    void deletePlaylist(Playlists playlists);

    @Query("DELETE FROM playlists WHERE id = :id")
    void deletePlaylistByID(int id);

    @Query("DELETE FROM playlists")
    void resetPlaylist();

}

SongsDao:

@Dao
public interface SongsDao {

    @Query("SELECT playlist_id from songs")
    Cursor queryPlaylists();

    @Query("SELECT * from Songs WHERE playlist_id like :playlistID")
    Cursor querySongsFromPlaylist(int playlistID);

    @Insert()
    void insertPlaylist(Songs songs);

    @Update()
    void updatePlaylist(Songs songs);

    @Delete()
    void deletePlaylist(Songs songs);

    @Query("DELETE FROM Songs")
    void resetPlaylist();

}

as you can see from the log outputs, the value inserted into index(corresponds to playlist_id) is exactly the same as the value i just queried from the database.

2019-10-03 18:58:59.538 16084-16084/com.lucas.darkplayer E/tesststaAETAST:: 3
2019-10-03 18:58:59.538 16084-16084/com.lucas.darkplayer E/INDEX:: 3

Answer 1

Everything you did seems to be pretty much okay, but you shouldn't have two separate databases for Playlist and Song. Instead, you should have only ONE database that contains two tables (Entities), which should be related to each other through a FOREIGN KEY CONSTRAINT.

Another thing I spotted is that you have two columns in your Playlist Entity that seem to have the same purpose: index (that you defined as an autogenerated PrimaryKey) and id. I am not sure why you did it like that, but I think you should only have one column named id and make that field your PRIMARY KEY.

Also, if you define a PrimaryKey as autogenerate = true, you don't need to worry about generating a unique id value. Instead, the database will take care of that automatically for you when you save an Entity instance for the first time in the database.