How to set Nullable primary key in Room Database Android

Question

While migrating sqlite database to RoomDatabase I am facing issue(java.lang.IllegalStateException: Migration didn't properly handle) with primary key.

SQLite code:

  @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_USERS_TABLE = "CREATE TABLE " + TABLE_USERS + "("
                + KEY_USER_ID + " TEXT PRIMARY KEY UNIQUE,"
                + KEY_USER_NAME + " TEXT,"
                + KEY_USER_PHONE + " INTEGER" + ")";

        db.execSQL(CREATE_USERS_TABLE);
    }

Migration code in Room DB:

database.execSQL("CREATE TABLE IF NOT EXISTS converted_user_details (userId TEXT PRIMARY KEY NOT NULL, userName TEXT, phoneNumber INTEGER );")

I am getting below exception:

Expected: 05-20 15:09:28.081 22755 22785 E AndroidRuntime: TableInfo{name='user_details', columns={userName=Column{name='userName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, userId=Column{name='userId', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='null'}, phoneNumber=Column{name='phoneNumber', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}

Found: 05-20 15:09:28.081 22755 22785 E AndroidRuntime: Found: 05-20 15:09:28.081 22755 22785 E AndroidRuntime: TableInfo{name='user_details', columns={phoneNumber=Column{name='phoneNumber', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, userName=Column{name='userName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, userId=Column{name='userId', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}

As per my understanding in Sqlite code while creating table primary key is not defined is "Non Null" but in Room we need to define primary key as "Non null" so getting exception. expected notNull=true & found notNull=false for UserId

Answer 1

In short you cannot utilise primary keys that do not have the NOT NULL constraint, when Room builds the schema it is always applied to primary keys.

• I can't remember exactly where but I recall somewhere that room documentation says that it considers the use of NULL for a primary key as a bug in SQLite and therefore enforces use of the NOT NULL constraint.

• If you compile using @PrimaryKey val userId: String? .... (Kotlin) then the compile results in the error :-

• 

• They are referencing

  • According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or a STRICT table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allows NULLs in most PRIMARY KEY columns.

When migrating, if not using auto migration, it is suggested that you copy the SQL, if creating a new table, from the createAllTables method, which is in the class that is the same name as the class(es) that are annotated with @database but suffixed with _Impl that can be found in the generated java after compiling the project (noting that the entities must be defined in the entities = parameter of the @Database annotation). This SQL will be as Expected.

If you are trying to utilise null values then you would have to determine an alternative approach.

Example of extracting the Expected schema (same for Java or Kotlin, but the latter used here)

Class User_details as :-

@Entity
data class User_details(
    @PrimaryKey
    val userId: String/*? Room will not allow the ? nullable for primary key*/,
    val userName: String?,
    val phoneNumber: Long?,
)

The @Database annotated class TheDatabase (note includes other tables) :-

@Database(entities = [College::class, Group::class,Student::class,GroupStudentMap::class,User_details::class], version = 1, exportSchema = false)
abstract class TheDatabase : RoomDatabase() {
    ....
}

After compile (Ctrl+F9) the locate the class that is the same name as the class that is annotated with @Database but suffixed with _Impl e.g.

Open the class and locate the createAllTables method :-