Search code examples
androidkotlinandroid-room

Why does @PrimaryKey val id: Int? = null works when creating a Room entity?

I'm following a tutorial on how to create a note app using Jetpack Compose. This is the link for the tutorial. There is a point in the tutorial that he creates this entity:

@Entity
data class Note(
    val title: String,
    val content: String,
    val timestamp: Long,
    val color: Int,
    @PrimaryKey val id: Int? = null
)

Please take note that he is using a nullable value for the primary key and that he is not using the (autoGenerate = true).

I would have thought that wouldn't work. First because the primary key should never be null and second because without the autoGenerate how will the id behave? Won't they all have the same id?

The code I have seen more often when creating room entities is this:

@PrimaryKey(autoGenerate = true) val id: Int = 0

How does the @PrimaryKey annotation works exactly? Why both ways work? Which one is more recommended?

Edit: I want to clarify that the code used in the tutorial compiles and it saves to the database properly.

Solution

  • I would have thought that wouldn't work. First because the primary key should never be null and second because without the autoGenerate how will the id behave?

    Room actually uses null, converting 0 to null if autogenerate is true as SQLite, in the special case of an INTEGER PRIMARY KEY, generates a value when null is specified.

    • As SQLite requires an INTEGER PRIMARY KEY to be an integer it handles the null, in this special case, generating the integer value. This is sort of explained by

    The PRIMARY KEY constraint for a rowid table (as long as it is not the true primary key or INTEGER PRIMARY KEY) is really the same thing as a UNIQUE constraint. Because it is not a true primary key, columns of the PRIMARY KEY are allowed to be NULL, in violation of all SQL standards.

    If autogenerate is true then the generated code (as per/from the demo below) includes:-

    "INSERT OR IGNORE INTO `AutoGenTrueTable` (`id`,`name`) VALUES (nullif(?, 0),?)"

    Whilst in the case of autogenerate being false the generated code instead uses:-

    "INSERT OR IGNORE INTO `AutoGenFalseTable` (`id`,`name`) VALUES (?,?)"

    • note that the OR IGNORE is because of the onConflictStrategy of IGNORE.

    • the latter example, i.e. autogenerate being false, will thus use the value 0 if the the id field is 0

    • The generated java can be found easily via the Android View of Android Studio. The DAOs being in the class that is the same name as the interface/abstract class but suffixed with _Impl.

    • the binding via the Android SQLite API used by Room converts the null to the null keyword (token).

      • The class named the same as the @Database annotated class but with the _Impl suffix, will have other useful code, such as in the createAllTables method where the SQL for creating the tables can be found.

    Won't they all have the same id?

    NO as a primary key is implicitly unique and therefore NEVER can an id be the same as another id in the same table if it is the primary key and irrespective of autogenerated being true or false.

    If autogenerate is true then Room also converts 0 to be no value provided and thus 0 results in a generated value.

    However if you specify a value of 0 and if autogenerate is false (explicitly or implicitly by default) then 0 will be used for the id which will not be allowed more than once, but can be handled by the onConflictStrategy of the insert.

    The DEMO below illustrates the above (noting that the IGNORE onConflictStrategy is used and hence the errant duplicated 0 id's are just ignored).

    A little bit about INTEGER PRIMARY KEY (e.g. @PrimaryKey val whatever:Int or more correctly Long) aka an alias of the rowid column.

    • Byte, Short and so on could be used, as they are integer types, but they would be of limited use.
    • Long is more correct as the value can be as large as a 64 bit signed integer (Int isn't large enough, Long is, for many situations this isn't an issue).

    If a column is specifically INTEGER (which Room determines at compile time) and is the PRIMARY KEY (either at the column or table level) then the column is an alias of a special normally hidden column, the rowid, that all tables have (except WITHOUT ROWID tables, which Room does not support via annotations).

    • note that the rowid column ALWAYS exists (unless the table is a WITHOUT ROWID table, again which Room does not support) and is always assigned an integer value (irrespective of whether or not INTEGER PRIMARY KEY is specified or implied).

    • although rowid is used SQLite accepts other aliases see https://www.sqlite.org/rowidtable.html for more on rowids

    Such a column must be an INTEGER type value (with the exception of the rowid column or an alias thereof any column type can actually store any type of value, although Room does not support this). Furthermore if no value is given for such a column when inserting then the value will be generated by SQLite. This will typically be 1 greater than the highest rowid for that table.

    Hence as long as the situation is that no value is provided for the column then the value will be generated (and will very likely be 1 greater than the highest value).

    If Room's autogenerate=true is used then that adds the SQLite AUTOINCREMNET keyword to the table definition/schema. This changes the way that the value is generated in that it is the greater of two values, one being the highest rowid in the table, the other being the highest recorded/ever used rowid value, which may be higher than the highest rowid, if the the row with the highest rowid has been deleted.

    • note this assumes that the sqlite_sequence table is not altered other than by SQLite's handling of the table (it can be manipulated BUT BEWARE)

    In short AUTOINCREMENT adds a constraint/rule that says that the generated value MUST be greater than any used. However, this requires that the highest assigned value must be stored elsewhere. SQLite stores this additional value in a table named sqlite_sequence which will have 1 row per table. There is an overhead of obtaining and maintaining such a value and hence why the SQLite document states:-

    • The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed. see https://www.sqlite.org/autoinc.html

    Using null or 0 is the same with autogenerate=true, Room does not provide a value and thus the value gets generated. If any other value is provided then that value is used (which will result in a unique conflict if a row already exists, the unique conflict handling with the onConFlictStrategy parameter of the respective annotation ('@Insert' or @Update) when inserting or updating)

    • if using an INSERT or UPDATE query then the actual SQLite OR ????? on conflict action can be specified e.g. 'INSERT OR IGNORE ....'

    As stated previously without autogenerate=true, 0 will be used as the value (see demo below), thus to not have the overheads/waste/inefficiency of AUTOINCREMENT then the field should be nullable and null used to have the value generated.

    • Java, where primitives (int, long), have a default value of 0 and cannot be null is/was a little different and have some gotchas.

    DEMO

    Perhaps consider the following demo where 3 tables (entities) are used one where autogenerate=true is used the other 2 where it is not specified and thus autogenerate=false is implied. The difference between the other two is that the first does not allow nulls for the id and the default id is 0, the second does and the default value for the id is null.

    The 3 @Entity annotated classes are:-

    @Entity
data class AutoGenTrueTable(
    @PrimaryKey(autoGenerate = true)
    val id: Long=0,
    val name: String
)
@Entity
data class AutoGenFalseTable(
    @PrimaryKey
    val id: Long=0,
    val name: String
)
@Entity
data class AutoGenFalseNullableTable(
    @PrimaryKey
    val id: Long?=null,
    val name: String
)

    To demonstrate sqlite_sequence (extract all data from it) then a POJO:-

    data class SQLiteSequence(
    val name: String,
    val seq: Long
)

    an @Dao annotated interface (catering for inserting, specialised deletions and extracting data):-

    @Dao
interface AllDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(autoGenTrueTable: AutoGenTrueTable): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(autoGenFalseTable: AutoGenFalseTable): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(autoGenFalseNullableTable: AutoGenFalseNullableTable): Long

    @Query("DELETE FROM autogentruetable WHERE id >=:highAsOrHigherThanId")
    fun deleteFromAGTTByHighIds(highAsOrHigherThanId: Long)
    @Query("DELETE FROM autogenfalsetable WHERE id >=:highAsOrHigherThanId")
    fun deleteFromAGFTByHighIds(highAsOrHigherThanId: Long)
    @Query("DELETE FROM autogenfalsenullabletable WHERE id >=:highAsOrHigherThanId")
    fun deleteFromFalseNullableByHighIds(highAsOrHigherThanId: Long)

    @Query("SELECT * FROM autogentruetable")
    fun getAllFromAutoGenTrue(): List<AutoGenTrueTable>
    @Query("SELECT * FROM autogenfalsetable")
    fun getAllFromAutoGenFalse(): List<AutoGenFalseTable>
    @Query("SELECT * FROM autogenfalsenullabletable")
    fun getAllFromAutoGenFalseNullable(): List<AutoGenFalseNullableTable>
    @Query("SELECT * FROM sqlite_sequence")
    fun getAllFromSQLiteSequence(): List<SQLiteSequence>
}

    A pretty straight forward @Database annotated abstract class, allowing the main thread to be used for the brevity of the demo:-

    @Database(entities = [AutoGenTrueTable::class,AutoGenFalseTable::class,AutoGenFalseNullableTable::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAOs(): AllDAOs

    companion object {
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context, TheDatabase::class.java, "the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

    Finally some activity code that inserts/extracts and deletes data, for all 3 demo tables, writing the extracted data, including the contents of sqlite_master to the log at various stages:-

    class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDAOs()
        var stage = 0
        logEverything(stage++)

        for (i: Int in 1..3) {
            dao.insert(AutoGenTrueTable(0,"AG_TT_ZERO_${i}"))
            //dao.insert(AutoGenTrueTable(null,"AG_TT_NULL_${i}")) not nullable cannot be used
            dao.insert(AutoGenTrueTable(name = "AG_TT_DEFAULT_${i}"))
            dao.insert(AutoGenTrueTable(id = 100,"AG_TT_100_${i}"))

            dao.insert(AutoGenFalseTable(0,"AG_FT_ZERO_${i}"))
            //dao.insert(AutoGenFalseTable(id = null,name = "AG_FT_NULL_${i}")) not nullable cannot be used
            dao.insert(AutoGenFalseTable(name = "AG_FT_DEFAULT_${i}"))
            dao.insert(AutoGenFalseTable(id = 100, "AG_FT_100_${i}"))

            dao.insert((AutoGenFalseNullableTable(0, "AG_FTNULL_ZERO_${i}") ))
            dao.insert((AutoGenFalseNullableTable(null, "AG_FTNULL_NULL_${i}") ))
            dao.insert((AutoGenFalseNullableTable( name = "AG_FTNULL_DEFAULT_${i}") ))
            dao.insert(AutoGenFalseNullableTable(id = 100, name = "AG_FTNULL_100_${i}"))
        }
        logEverything(stage++)
        
        
        dao.deleteFromAGTTByHighIds(100)
        dao.deleteFromAGFTByHighIds(100)
        dao.deleteFromFalseNullableByHighIds(100)

        logEverything(stage++)
        
        
        for (i: Int in 1..3) {
            dao.insert(AutoGenTrueTable(0,"AG_TT_ZERO_${i}"))
            //dao.insert(AutoGenTrueTable(null,"AG_TT_NULL_${i}")) not nullable cannot be used
            dao.insert(AutoGenTrueTable(name = "AG_TT_DEFAULT_${i}"))
            dao.insert(AutoGenTrueTable(id = 100,"AG_TT_100_${i}"))

            dao.insert(AutoGenFalseTable(0,"AG_FT_ZERO_${i}"))
            //dao.insert(AutoGenFalseTable(id = null,name = "AG_FT_NULL_${i}")) not nullable cannot be used
            dao.insert(AutoGenFalseTable(name = "AG_FT_DEFAULT_${i}"))
            dao.insert(AutoGenFalseTable(id = 100, "AG_FT_100_${i}"))

            dao.insert((AutoGenFalseNullableTable(0, "AG_FTNULL_ZERO_${i}") ))
            dao.insert((AutoGenFalseNullableTable(null, "AG_FTNULL_NULL_${i}") ))
            dao.insert((AutoGenFalseNullableTable( name = "AG_FTNULL_DEFAULT_${i}") ))
            dao.insert(AutoGenFalseNullableTable(id = 100, name = "AG_FTNULL_100_${i}"))
        }
        logEverything(stage++)
    }

    fun logEverything(stage: Int) {
        Log.d("DBINFO_STARTSTAGE_${stage}","Starting logging of stage ${stage}")
        logAllFromAGTT(stage)
        logAllFromAGFT(stage)
        logAllFromAGFTN(stage)
        logAllFromSQLite_Sequence(stage)
    }

    fun logAllFromAGTT(stage: Int) {
        for(a in dao.getAllFromAutoGenTrue()) {
            Log.d("DBINFO_AGTT_STG${stage}","ID is ${a.id} NAME is ${a.name}")
        }
    }
    fun logAllFromAGFT(stage: Int) {
        for(a in dao.getAllFromAutoGenFalse()) {
            Log.d("DBINFO_AGFT_STG${stage}","ID is ${a.id} NAME is ${a.name}")
        }
    }

    fun logAllFromAGFTN(stage: Int) {
        for(a in dao.getAllFromAutoGenFalseNullable()) {
            Log.d("DBINFO_AGFTN_STG${stage}","ID is ${a.id} NAME is ${a.name}")
        }
    }

    fun logAllFromSQLite_Sequence(stage: Int) {
        for(ss in dao.getAllFromSQLiteSequence()) {
            Log.d("DBINFO_SSEQ_STG${stage}","TABLE IS ${ss.name} HIGHEST ID STORED FOR THE TABLE IS ${ss.seq}")
        }
    }
}

    When run for the first time the App is installed then the output is (2 blanks lines between stages and a blank line between the 3 tables):-

    2023-04-14 12:01:26.073  D/DBINFO_STARTSTAGE_0: Starting logging of stage 0


2023-04-14 12:01:26.244  D/DBINFO_STARTSTAGE_1: Starting logging of stage 1
2023-04-14 12:01:26.246  D/DBINFO_AGTT_STG1: ID is 1 NAME is AG_TT_ZERO_1
2023-04-14 12:01:26.246  D/DBINFO_AGTT_STG1: ID is 2 NAME is AG_TT_DEFAULT_1
2023-04-14 12:01:26.246  D/DBINFO_AGTT_STG1: ID is 100 NAME is AG_TT_100_1
2023-04-14 12:01:26.246  D/DBINFO_AGTT_STG1: ID is 101 NAME is AG_TT_ZERO_2
2023-04-14 12:01:26.246  D/DBINFO_AGTT_STG1: ID is 102 NAME is AG_TT_DEFAULT_2
2023-04-14 12:01:26.247  D/DBINFO_AGTT_STG1: ID is 103 NAME is AG_TT_ZERO_3
2023-04-14 12:01:26.247  D/DBINFO_AGTT_STG1: ID is 104 NAME is AG_TT_DEFAULT_3

2023-04-14 12:01:26.249  D/DBINFO_AGFT_STG1: ID is 0 NAME is AG_FT_ZERO_1
2023-04-14 12:01:26.249  D/DBINFO_AGFT_STG1: ID is 100 NAME is AG_FT_100_1

2023-04-14 12:01:26.250  D/DBINFO_AGFTN_STG1: ID is 0 NAME is AG_FTNULL_ZERO_1
2023-04-14 12:01:26.250  D/DBINFO_AGFTN_STG1: ID is 1 NAME is AG_FTNULL_NULL_1
2023-04-14 12:01:26.250  D/DBINFO_AGFTN_STG1: ID is 2 NAME is AG_FTNULL_DEFAULT_1
2023-04-14 12:01:26.250  D/DBINFO_AGFTN_STG1: ID is 100 NAME is AG_FTNULL_100_1
2023-04-14 12:01:26.251  D/DBINFO_AGFTN_STG1: ID is 101 NAME is AG_FTNULL_NULL_2
2023-04-14 12:01:26.251  D/DBINFO_AGFTN_STG1: ID is 102 NAME is AG_FTNULL_DEFAULT_2
2023-04-14 12:01:26.251  D/DBINFO_AGFTN_STG1: ID is 103 NAME is AG_FTNULL_NULL_3
2023-04-14 12:01:26.251  D/DBINFO_AGFTN_STG1: ID is 104 NAME is AG_FTNULL_DEFAULT_3
2023-04-14 12:01:26.253  D/DBINFO_SSEQ_STG1: TABLE IS AutoGenTrueTable HIGHEST ID STORED FOR THE TABLE IS 104


2023-04-14 12:01:26.258  D/DBINFO_STARTSTAGE_2: Starting logging of stage 2
2023-04-14 12:01:26.261  D/DBINFO_AGTT_STG2: ID is 1 NAME is AG_TT_ZERO_1
2023-04-14 12:01:26.261  D/DBINFO_AGTT_STG2: ID is 2 NAME is AG_TT_DEFAULT_1

2023-04-14 12:01:26.262  D/DBINFO_AGFT_STG2: ID is 0 NAME is AG_FT_ZERO_1

2023-04-14 12:01:26.263  D/DBINFO_AGFTN_STG2: ID is 0 NAME is AG_FTNULL_ZERO_1
2023-04-14 12:01:26.263  D/DBINFO_AGFTN_STG2: ID is 1 NAME is AG_FTNULL_NULL_1
2023-04-14 12:01:26.263  D/DBINFO_AGFTN_STG2: ID is 2 NAME is AG_FTNULL_DEFAULT_1
2023-04-14 12:01:26.264  D/DBINFO_SSEQ_STG2: TABLE IS AutoGenTrueTable HIGHEST ID STORED FOR THE TABLE IS 104


2023-04-14 12:01:26.333  D/DBINFO_STARTSTAGE_3: Starting logging of stage 3

2023-04-14 12:01:26.336  D/DBINFO_AGTT_STG3: ID is 1 NAME is AG_TT_ZERO_1
2023-04-14 12:01:26.336  D/DBINFO_AGTT_STG3: ID is 2 NAME is AG_TT_DEFAULT_1
2023-04-14 12:01:26.337  D/DBINFO_AGTT_STG3: ID is 100 NAME is AG_TT_100_1
2023-04-14 12:01:26.337  D/DBINFO_AGTT_STG3: ID is 105 NAME is AG_TT_ZERO_1
2023-04-14 12:01:26.337  D/DBINFO_AGTT_STG3: ID is 106 NAME is AG_TT_DEFAULT_1
2023-04-14 12:01:26.337  D/DBINFO_AGTT_STG3: ID is 107 NAME is AG_TT_ZERO_2
2023-04-14 12:01:26.337  D/DBINFO_AGTT_STG3: ID is 108 NAME is AG_TT_DEFAULT_2
2023-04-14 12:01:26.337  D/DBINFO_AGTT_STG3: ID is 109 NAME is AG_TT_ZERO_3
2023-04-14 12:01:26.337  D/DBINFO_AGTT_STG3: ID is 110 NAME is AG_TT_DEFAULT_3

2023-04-14 12:01:26.340  D/DBINFO_AGFT_STG3: ID is 0 NAME is AG_FT_ZERO_1
2023-04-14 12:01:26.340  D/DBINFO_AGFT_STG3: ID is 100 NAME is AG_FT_100_1

2023-04-14 12:01:26.342  D/DBINFO_AGFTN_STG3: ID is 0 NAME is AG_FTNULL_ZERO_1
2023-04-14 12:01:26.342  D/DBINFO_AGFTN_STG3: ID is 1 NAME is AG_FTNULL_NULL_1
2023-04-14 12:01:26.342  D/DBINFO_AGFTN_STG3: ID is 2 NAME is AG_FTNULL_DEFAULT_1
2023-04-14 12:01:26.342  D/DBINFO_AGFTN_STG3: ID is 3 NAME is AG_FTNULL_NULL_1
2023-04-14 12:01:26.343  D/DBINFO_AGFTN_STG3: ID is 4 NAME is AG_FTNULL_DEFAULT_1
2023-04-14 12:01:26.343  D/DBINFO_AGFTN_STG3: ID is 100 NAME is AG_FTNULL_100_1
2023-04-14 12:01:26.343  D/DBINFO_AGFTN_STG3: ID is 101 NAME is AG_FTNULL_NULL_2
2023-04-14 12:01:26.343  D/DBINFO_AGFTN_STG3: ID is 102 NAME is AG_FTNULL_DEFAULT_2
2023-04-14 12:01:26.343  D/DBINFO_AGFTN_STG3: ID is 103 NAME is AG_FTNULL_NULL_3
2023-04-14 12:01:26.343  D/DBINFO_AGFTN_STG3: ID is 104 NAME is AG_FTNULL_DEFAULT_3
2023-04-14 12:01:26.346  D/DBINFO_SSEQ_STG3: TABLE IS AutoGenTrueTable HIGHEST ID STORED FOR THE TABLE IS 110

    The Results explained (a little)

    It can clearly be seen that id's aren't duplicated (they cannot be as the primary key in all cases is implicitly unique) and also that sqlite_sequence only records the highest ever used id for the TT table (the one with autogenerate=true).

    Not so easy to see is that where autogenerate is false, at least be implicata ion/default, that using any value more than once, 0 included, will not generate the id, that is Room passes that value to the insert. The demo has the IGNORE onConflictStrategy so these attempted duplicates are ignored an no failure occurs.

    Hence the ???_ZERO_nn, for the FT and FTNULL have an id of 0 and only the single row where nn is 1. Unlike the TT_ZERO where all 3 rows have been inserted with a generated ID that is not 0.