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 SQLite Autoincrement the first line says it all:-
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.
Additional
Re the comment:-
Thanks for the answer... the Room docs specifically state that for primary keys, if the ID field is initialized to 0, as it will be for an uninitialized Java primitive like long, the ID will be treated as "empty" and the auto-generated value will be applied... So, long vs Long shouldn't be the issue. Nevertheless, I tried switching to Long and leaving it uninitialized (i.e., null). Now Room throws a null pointer exception for Attempt to invoke virtual method 'long java.lang.Long.longValue()' on a null object reference. However, the docs say a null Long is acceptable too. I'm at a loss...
Here's a working example that displays what is covered above. It uses 3 classes, the original, a fix (using Long instead of long with autogenerate=true) and the suggested more efficient using Long without autogenerate = true.
The classes are:-
Foo
@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;
}
}
Foo1 (fix)
@Entity(tableName="foo1")
public class Foo1 {
@PrimaryKey(autoGenerate = true)
private Long id;
public Foo1() {}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
}
and Foo2 (suggested)
@Entity(tableName="foo2")
public class Foo2 {
@PrimaryKey
private Long id;
public Foo2() {}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
}
With the class annotated with @Dao :-
@Insert(onConflict = IGNORE)
abstract long insert(Foo foo);
@Insert(onConflict = IGNORE)
abstract long insert(Foo1 foo1);
@Insert(onConflict = IGNORE)
abstract long insert(Foo2 foo2);
- abstract class rather than interface used
- IGNORE so that the UNIQUE constraint conflict doesn't fail
And finally the following code :-
for(int i = 0; i < 3; i++) {
dao.insert(new Foo());
dao.insert(new Foo1());
dao.insert(new Foo2());
}
Results via App Inspection show:-
sqlite_sequence (hidden by App Inspection so accessed via query)
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.
