Room database: NOT NULL constraint failed at deletion

Question

I have the following entities, Session and Label:

@Entity(
    foreignKeys =
    @ForeignKey(
            entity = Label.class,
            parentColumns = {"id", "archived"},
            childColumns = {"labelId", "archived"},
            onUpdate = CASCADE,
            onDelete = SET_DEFAULT))
    public class Session {

    @PrimaryKey(autoGenerate = true)
    public long id;

    @Nullable
    public String label = null;

    public boolean archived = false;
}

@Entity(primaryKeys = {"id", "archived"})
public class Label {

    @NonNull
    public String id;

    public boolean archived = false;
}

When deleting a Label attached to a Session I'm getting a NOT NULL constraint failed for Session.archived.

What am I doing wrong here?

Answer 1

I believe that the issue is with using a type of boolean as in public boolean archived = false;.

As boolean is a Java primary type then it has an implicit @NonNull (the table is created with the NOT NULL constraint)

With Room you cannot (I beleive) set default values that are generated in the SQLite table so when using onDelete SET_DEFAULT the default value will be NULL as no actual default value has been set.

• Note Room does now support default values using defaultValue() within the @ColumnInfo annotation.

• using = false does not affect the actual SQLite table i.e. the column definition, if using Boolean, will be labelId INTEGER not labelId INTEGER DEFAULT 0.

Assuming that NULL is acceptable, then you can use Boolean (the Object) instead of boolean (the primary type). Objects can be null.

i.e. in Session use

public Boolean archived = false;

Visual explantion :-

The following is the code, generated by Room, for the Session Entity but with an additional column added using :-

.........
public boolean archived = false;
public Boolean other_archived = false; //<<<<<<<<<< ADDED
........

Code Generated by Room :-

CREATE TABLE IF NOT EXISTS `Session` (
    `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    `label` TEXT, 

    `archived` INTEGER NOT NULL, /*<<<<<<<<<< boolean so NOT NULL */ 
    `other_archived` INTEGER, /*<<<<<<<<<< Boolean so no NOT NULL */
    FOREIGN KEY(`label`, `archived`) REFERENCES `Label`(`id`, `archived`) ON UPDATE CASCADE ON DELETE SET DEFAULT)

---

Extra

If you wanted the archived column in the Session table to be 0 (false), then you would have to modify the table. This would entail

1. Creating a replacement table that is the same except that the label column definition would be label INTEGER DEFAULT 0 (i.e. DEFAULT 0 is added)
2. Copying the data from the Session table into the replacement table e.g. INSERT INTO replacement_Session SELECT * FROM Session;
3. Rename the Session table .
4. Rename the replacement table to Session.
5. If happy Drop the renamed Session table.

Alternately (I think) you could use an AFTER UPDATE TRIGGER to change the NULL to 0 (false).

Either option would have to be done outside of Room or before the RoomDatabase has been built.

Answer 2

Referred to this. If you use Kotlin for coding, for making field of models null-able don't use @Nullable instead make field nullable by using "?". following fields are not nullable.

@Entity(tableName = "books")
data class Book(
    @PrimaryKey
    val id: Int,
    val title: String,
    val description: String,
    val info: String,
    val type: Int,
    val url: String
)

but following field are nullable:

@Entity(tableName = "books")
data class Book(
    @PrimaryKey
    val id: Int,
    val title: String?,
    val description: String?,
    val info: String?,
    val type: Int,
    val url: String?
)