Nullable foreign key on room

Question

I would know if that is possible to create a possible null reference on foreign key with room.

For now my database structure is like this :

@Entity(tableName = "A")
class A {
    @PrimaryKey(autoGenerate = true)
    public long id;
}

@Entity(tableName = "B")
class B{
    @PrimaryKey(autoGenerate = true)
    public long id;
}

@Entity(tableName = "C", foreignKeys = {@ForeignKey(entity = A.class, parentColumns = "id", childColumns = "foreign_id_a"), @ForeignKey(entity = B.class, parentColumns = "id", childColumns = "foreign_id_b")})
class C{
    public long id;
    public long foreign_id_a;
    public long foreign_id_b;   
}

I would like to be able to insert the following objects :

C(id=1, foreign_id_a=1, foreign_id_b=1)
C(id=1, foreign_id_a=null, foreign_id_b=1)
C(id=1, foreign_id_a=1, foreign_id_b=null)

But the previous insert with null value give this error : FOREIGN KEY constraint failed (Sqlite code 787 SQLITE_CONSTRAINT_FOREIGNKEY)

Is there a way to make it possible ?

Vladimír Bielený · Accepted Answer · 2021-05-21T18:25:50.393

7

Yes, just change foreign key types from long to Long.

In Java, long type cannot be null and therefore Room generates this column as NOT NULL. Also, the C class does not have @PrimaryKey specified.

@Entity(tableName = "C", foreignKeys = {@ForeignKey(entity = A.class, parentColumns = "id", childColumns = "foreign_id_a"), @ForeignKey(entity = B.class, parentColumns = "id", childColumns = "foreign_id_b")})
class C{
    @PrimaryKey
    public long id;
    public Long foreign_id_a;
    public Long foreign_id_b;   
}

In Kotlin, Long type is non-null. If you want to insert nullable foreign keys, you need to change the fields to nullable type Long?.

@Entity(tableName = "C", foreignKeys = [ForeignKey(entity = A::class, parentColumns = ["id"], childColumns = ["foreign_id_a"]), ForeignKey(entity = B::class, parentColumns = ["id"], childColumns = ["foreign_id_b"])])
class C{
    @PrimaryKey
    var id: Long = 0
    var foreign_id_a: Long? = null
    var foreign_id_b: Long? = null
}

edited May 21 '21 at 18:25

answered Jun 07 '19 at 11:13

Vladimír Bielený

2,795
2
11
16

What if we use Kotlin? – TheFedex87 May 19 '21 at 18:01
@TheFedex87 you can just use nullable type `Long?`, please see the edit. – Vladimír Bielený May 21 '21 at 18:26
I had already tried, but even if I use Long? and try to use a null key I get an error of constraint failed – TheFedex87 May 24 '21 at 15:06
Then you might have a different problem. Are you inserting object with multiple foreign keys? If you are inserting non null foreign keys, do corresponding rows with primary keys exist in the table you are referencing? – Vladimír Bielený May 26 '21 at 06:27

Nullable foreign key on room

1 Answers1

Linked