Why instrumented room test get error FOREIGN KEY constraint failed

Question

I don't understand this error, I have two entities Task and Project, one task is assign to one project (see code below).

But when I want test to add a task, I have this error: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)

I try to add onDelete = CASCADE to FOREIGNKEY but my test does not work :/

Task Class

@Entity(tableName = "task_table",
        foreignKeys = @ForeignKey(entity = Project.class,
        parentColumns = "id",
        childColumns = "projectId"))

public class Task {
    /**
     * The unique identifier of the task
     */
    @PrimaryKey(autoGenerate = true)
    public long id;

    private long projectId;

    
    @SuppressWarnings("NullableProblems")
    @NonNull
    private String name.

    private long creationTimestamp;

    public Task(long projectId, @NonNull String name, long creationTimestamp) {
        this.setProjectId(projectId);
        this.setName(name);
        this.setCreationTimestamp(creationTimestamp);
    }
...

TaskDao

@Dao
public interface TaskDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    void insert(Task task);


    @Query("DELETE  FROM task_table WHERE id = :id")
    void delete(long id);

    @Query("SELECT * FROM task_table ORDER BY id ASC")
    LiveData<List<Task>> getAllTasks();

    @Query("SELECT * FROM task_table WHERE id = :id")
    LiveData<Task> getTaskById(long id);
}

Project Class

@Entity(tableName = "project_table")
public class Project {
    /**
     * The unique identifier of the project
     */
    @PrimaryKey(autoGenerate = true)
    public long id;

    @NonNull
    private  String name;

    @ColorInt
    private  int color;

    public Project(@NonNull String name, @ColorInt int color) {
        this.name = name;
        this.color = color;
    }

ProjectDao

@Dao
public interface ProjectDao {


    @Insert(onConflict = OnConflictStrategy.IGNORE)
    void insert(Project project);

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    void insertAll(List<Project> projects);


    @Query("SELECT * FROM project_table")
    LiveData<List<Project>> getAllProjects();


    @Query("SELECT * FROM project_table WHERE id = :id")
    LiveData<Project> getProjectById(long id);

}

TestInstrumented:

 @Before
    public void createDb() {
        this.database = Room.inMemoryDatabaseBuilder(ApplicationProvider.getApplicationContext(),
                AppDatabase.class)
                .allowMainThreadQueries()
                .build();

        taskDao = database.taskDao();
        projectDao = database.projectDao();
    }

    @After
    public void closeDb() {
        database.close();
    }
@Test
    public void addTask() {

        Project project = new Project("toto", 0xFFB4CDBA);
        this.projectDao.insert(project);
        long projectId = project.getId();

        assertThat(projectId, equalTo(project.getId()));

        Task task = new Task(projectId, "tâche 1", new Date().getTime());

        this.taskDao.insert(task);
}

If someone can help it'll be very kind, I don't know how I can resolve this.

Thanks very much for your help

Answer 1

The project id will be 0 as it hasn't been set according to the inserted value when you use long projectId = project.getId();.

Thus the ForeignKey conflict when inserting the Task as the id column of the project will have been generated by SQLite and WILL not be 0 (it will be 1 or greater).

Change

@Insert(onConflict = OnConflictStrategy.IGNORE)
void insert(Project project);

to (to get the actual generated id)

@Insert(onConflict = OnConflictStrategy.IGNORE)
long insert(Project project); // will return the id that has been generated 

and then use :-

    Project project = new Project("toto", 0xFFB4CDBA);
    long projectId = this.projectDao.insert(project); //<<<<< returns the actual id
    project.setId(projectId); // sets project id according to the inserted id BUT only if inserted and not ignored when returned value will be -1
    // You should really check for -1 and handle accordingly
   
    assertThat(projectId, equalTo(project.getId())); // not really of any use

    Task task = new Task(projectId, "tâche 1", new Date().getTime());
    this.taskDao.insert(task);

• Note, the above is in-principle code, it has not been compiled or run/tested. It may therefore contain some errors.