Pretty much what it says on the tin: I'm using Room 2.4.1 on Android to store some data. I have an entity which is set to have an auto-generated primary key. However, I can only do one insert of an instance of that entity (which sets the primary key field to 0). After that, the application crashes because SQLite is throwing unique key violations for the primary key field. This shouldn't happen, given that the primary key field is supposed to be auto-generated... How can I stop this from happening? I can, of course, manage incrementing the key myself, but that defeats the point of Room having this feature.
Here's my Room entity (with additional columns stripped out for simplicity)...
import androidx.room.ColumnInfo;
import androidx.room.Entity;
import androidx.room.PrimaryKey;
import android.location.Location;
import android.os.Build;
@Entity(tableName="foo")
public class Foo {
@PrimaryKey(autoGenerate=true)
private long id;
public Foo() {
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
}
It has an associated DAO...
import androidx.room.Dao;
import androidx.room.Insert;
@Dao
public interface FooDao {
@Insert
long insert(Foo foo);
}
I try to insert (note that I can do this once successfully, but if I try to generate an insert a second Foo, the error crops up)...
Foo foo = new Foo();
long fooId = fooDao.insert(foo);
And I get the following stacktrace...
2022-01-28 14:28:01.027 15233-15278/com.bar.baz E/AndroidRuntime: FATAL EXCEPTION: StateController
Process: com.bar.baz, PID: 15233
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: foo.id (code 1555)
at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:783)
at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeInsert(SourceFile:51)
at androidx.room.EntityInsertionAdapter.insertAndReturnId(SourceFile:114)
at com.bar.baz.database.FooDao_Impl.insert(SourceFile:89)
at ...
CodePudding user response：
Your issue is that as a primitive long defaults to 0 and hence the id is being set to 0 and the UNIQUE conflict (see below for a fuller explanation of why this appears to contradict the documentation).
I would suggest using :-
@Entity(tableName="foo")
public class Foo {
@PrimaryKey
private Long id=null;
public Foo() {
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
}
Long as opposed to long does not have a default value, it is null. Room sees the null/primary key combination and skips adding a value so the value in generated.
This has the advantage of not using the inefficient AUTOINCREMENT (what using Room's autogenerate=true turns on). The id column will still be generated, be unique and typically be 1 greater than the highest id that exists in the table.
Whilst, autogenerate = true ( AUTOINCREMENT) adds an additional rule The rule being that an automatically generated rowid number has to be higher than the last ever used. To enable this a table sqlite_sequence is used (created automatically for the first instance of AUTOINCREMENT) which stores the last assigned rowid value.
- rowid is a hidden column that, at least for Room tables, always exists. When you have an integer type (boolean -> long either primitive or object) and the column is the primary key, then that column is an alias of the rowid.
As such when using autogenerate = true, there are overheads in both searching and maintain this extra table.
See 
So from this it can be seen to the foo table's last insert was 0 and that foo1's last insert was 3 and that there is no row for the foo2 table (but as can also be seen the table exists, ignore the MainTypeEntity and EmbeddedTypeEnitity from another question).
Foo :-
As can be seen the first row was inserted BUT with an id of 0. This proves that Room is using the value of the primitive i.e. 0 and NOT using a generated value because of (see link above - section 2 - last but 1 paragraph)
If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used.
Foo1
As can be seen all 3 rows have been inserted, and that the first id is 1 not 0. From the sqlite_sequence table above Foo1 uses autoincrement aka autogenerate = true.
Foo2
Th documentation does say :-
If the field type is long or int (or its TypeConverter converts it to a long or int), Insert methods treat 0 as not-set while inserting the item.
However, this is not the full truth and nothing but the full truth. It does not go on to elaborate on when this does not apply. If you looked at the build log you would have seen a warning like:-
warning: ... .Foo's id field has type long but its getter returns java.lang.Long. This mismatch might cause unexpected id values in the database when a.a.so70867141jsonstore.Foo is inserted into database.
private long id;
So getters (or setters) can overrule.
Now changing Foo to be
@Entity(tableName="foo")
public class Foo {
@PrimaryKey(autoGenerate = true)
public long id;
public Foo() {}
public Long getId() {
return id;
}
@Ignore //<<<<<<<<<< (plus id being public)
public void setId(Long id) {
this.id = id;
}
}
or :-
@Entity(tableName="foo")
public class Foo {
@PrimaryKey(autoGenerate = true)
public long id;
public Foo() {}
@Ignore //<<<<<<<<<<
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
}
or both @Ignore's then Foo works and id's are autogenerated.
CodePudding user response：
The issue here is an accidental mismatch: the primary key (id) field in the Foo class is a long, but the getter for id (getId()) returns a Long.
The Room docs state the following about primary keys...
If the field type is long or int (or its TypeConverter converts it to a long or int), Insert methods treat 0 as not-set while inserting the item. If the field's type is Integer or Long (or its TypeConverter converts it to an Integer or a Long), Insert methods treat null as not-set while inserting the item.
In the code that caused the issue, id was uninitialized, and therefore took on the value of 0. If the getter returned a long, Room would have considered it un-set and would have auto-generated an ID value. However, because the getter returns a Long, Room interpreted the 0 as the intended value for the primary key field on every insert, leading to the unique key constraint violation.
Android Studio / Room do produce helpful warnings when such a mismatch occurs, which should be heeded...
warning: com.company.project.database.Foo's id field has type long but its getter returns java.lang.Long. This mismatch might cause unexpected id values in the database when com.company.project.database.Foo is inserted into database.
The solution for auto-generating primary keys is to have a primitive long in the field, have that value set to 0 (or uninitialized, which will be 0), and have a getter that returns a long. Alternately, you could have a Long in the field, have that value set to null (or uninitialized, which will be null), and have a getter that returns a Long. You simply can't mix and match.
Please see MikeT's answer for additional performance recommendations around auto-generating primary keys with Room.