Is there any way to make Room generate an autogenerating primary key without AUTOINCREMENT?

Question

My Room database has the following entity:

@Entity
public class SmsMessage {
    @PrimaryKey
    public long id;

    public boolean incoming;
    public String sender;
    public String receiver;
    public String body;
    public long timestamp;
}

This fails with the following RuntimeException when trying to insert more than one item into the database:

SQLiteConstraintException: PRIMARY KEY must be unique (code 19)

The generated SQL CREATE TABLE statement looks like this:

CREATE TABLE `SmsMessage` (
    `id`    INTEGER NOT NULL,
    `incoming`  INTEGER NOT NULL,
    `sender`    TEXT,
    `receiver`  TEXT,
    `body`  TEXT,
    `timestamp` INTEGER NOT NULL,
    PRIMARY KEY(`id`)
);

This seems to be different from INTEGER NOT NULL PRIMARY KEY, even though I can't find any documentation for this behaviour in the SQLite documentation.

It seems I have to use @PrimaryKey (autogenerate=true) in order to make Room automatically generate primary key values. Looking at the generated database when using autogenerate=true, this generates the following SQL:

CREATE TABLE `SmsMessage` (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `incoming`  INTEGER NOT NULL,
    `sender`    TEXT,
    `receiver`  TEXT,
    `body`  TEXT,
    `timestamp` INTEGER NOT NULL
);

It seems that autogenerate=true corresponds to SQLite AUTOINCREMENT. However, the SQLite documentation makes quite clear that AUTOINCREMENT isn't needed (and in most cases not recommended) in order to automatically generate unique primary keys. The purpose of AUTOINCREMENT is basically to prevent re-use of used but deleted primary keys.

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.

In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

So it seems that using @PrimaryKey(autogenerate=true) is usually not needed nor recommended. But only using @PrimaryKey alone will not automatically generate values at all.

How do I tell Room that what I want is 'id' INTEGER NOT NULL PRIMARY KEY?

Answer 1

That's not possible at the moment - the only option is AUTOINCREMENT. You can star the existing feature request for progress updates on support for this use case.