Room unique constraint failed for primary key despite autoGenerate=true

Question

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 ...

Answer 1

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.