Is PrimaryKey's autoGenerate exactly equivalent to SQLite's AUTOINCREMENT?-CodePudding

Is marking a primary key with @PrimaryKey(autoGenerate = true) exactly the same as if you had used PRIMARY KEY AUTOINCREMENT in an SQL statement?

Intuition tells me yes, but documentation seems to suggest no.

Room javadoc states:

Set to true to let SQLite generate the unique id.

as if setting it false will prevent SQLite from generating the key.

But SQLite documentation for AUTOINCREMENT states that SQLite always generates a currently-unique key if none is given when doing an INSERT, and that AUTOINCREMENT merely adds the additional behavior that SQLite will never allow an automatically generated key to overlap with any previously deleted row.

The SQLite documentation also recommends not using AUTOINCREMENT if it isn't needed (for performance reasons), and states that it is usually not needed. From the description, that seems to match my case. My table will be fine if a previously deleted row ID gets reused.

CodePudding user response：

Is marking a primary key with @PrimaryKey(autoGenerate = true) exactly the same as if you had used PRIMARY KEY AUTOINCREMENT in an SQL statement?

Yes, as using autoGenerate=true adds the AUTOINCREMENT keyword.

But

as if setting it false will prevent SQLite from generating the key.

Is false.

If a class is:-

annotated with @Entity, and
the column/variable/member is annotated with @PrimaryKey, and
if the type resolves to an integer type
- (byte .... double, primitive or Object (e.g. Double))

then the value can be generated (it is INTEGER PRIMARY KEY that makes the column a special column that can be generated as that column is then an alias of the rowid (a normally hidden column)).

AUTOINCREMENT is only applicable to aliases of the rowid (i.e. INTEGER PRIMARY KEY). It does not determine whether the value can be generated (in the absence of a value for the column or when the value is null).

What AUTOINCREMENT does is add an additional rule when generating the value. That rule being that the value MUST be higher than any ever used for that table.

There are subtle differences.

Without AUTOINCREMENT

deleting the row with the highest value, frees that value for subsequent use (and would be used to generate the value still higher than any other value that exists at that time), and
should the highest value (9223372036854775807) be reached SQLite will try to find a free lower value, and
lastly it is possible to double the range of values by using negative values.

With AUTOINCREMENT

deleting the row with the highest value does not free that value for subsequent use
should the highest value (9223372036854775807) be reached then subsequent attempts to insert with a generated value will fail with an SQLITE FULL error.
- If you insert 1 row with a value of 9223372036854775807 then that's the only row that can be inserted.
negative values cannot be generated (can still be used)
an additional table is required (sqlite_sequence), which is automatically created by SQLite, that will have a row per table with AUTOINCREMENT. The highest used value is stored in the row. So whenever inserting when the value is to be generated requires the respective row to be retrieved and the value obtained, after insertion the value has to be updated. As such there are overheads associated with using AUTOINCREMENT.
Note the above is assuming that methods to circumvent SQLite's in-built handling are not circumvented (such as updating values in the sqlite_sequence table).

I would always advocate using (not using autoGenerate=true) e.g.

@PrimaryKey
Long id_column=null;

@PrimaryKey
var id_column: Long?=null

thus an @Insert (convenience insert) will autogenerate if no value is given for the id_column.