Primary keys in Room should be Int or Long?

Question

I'm designing a Database that would be implemented in Android using Room, after reading the docs I found that there is no recomendations about using Int or Long as primary keys.

In some places they define entities with int primary keys:

@Entity
data class User(
    @PrimaryKey var id: Int,
    var firstName: String?,
    var lastName: String?
)

But in other place it says that if you want to get the ID of the last row inserted "insert" method return a long.

@Dao
interface MyDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertUsers(vararg users: User)

    @Insert
    fun insertBothUsers(user1: User, user2: User)

    @Insert
    fun insertUsersAndFriends(user: User, friends: List<User>)
}

If the @Insert method receives only 1 parameter, it can return a long, which is the new rowId for the inserted item. If the parameter is an array or a collection, it should return long[] or List instead.

So, The primary keys in room should be Int or Long?; Are there best practices about choosing one type over the other?

Answer 1

Both of these types will map to an INTEGER in the underlying SQLite database.

For example, with a class like this:

@Entity
data class Test(@PrimaryKey val i: Int, val l: Long)

You'd get a SQLite table defined with this query:

CREATE TABLE IF NOT EXISTS `Test` (`i` INTEGER NOT NULL, `l` INTEGER NOT NULL, PRIMARY KEY(`i`))

So you can use whichever one you'll need the magnitude of in your code. If you do decide to use an Int for some reason and you run out of values, you can even change it to a Long without having to migrate your database later on.

As for this INTEGER type, as per the SQLite documentation:

The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

Answer 2

Both is fine. On mobile (and most of the time in general) Int should be sufficient (it will also save you 4 bytes over Long).

Why? Using an Int you could store over 2 billion records (2_000_000_000). So you could store a record of around 1/4 of all the humans living on earth. Just for comparison: Using a Long would enable you to store over 900 quadrillion records (900_000_000_000_000_000).