How to Make Primary Key as Autoincrement for Room Persistence Lib

How to make primary key as autoincrement for Room Persistence lib

You need to use the autoGenerate property

Your primary key annotation should be like this:

@PrimaryKey(autoGenerate = true)

Reference for PrimaryKey.

How to auto increment primary key in Room?

You can simply:

data class FavouriteEntity(
    @PrimaryKey(autoGenerate = true)
    val id : Int = 0 
)

or

data class FavouriteEntity(
    @PrimaryKey(autoGenerate = true)
    val id : Int? = null 
)

and the id field will auto-generate/auto-increment the id field.

Then use it with named parameters: e.g.

data class FavouriteEntity(
        val id: Int = 0,
        val name: String
)

fun main() {
    FavouriteEntity(name = "name")
}

How to make Primary key Auto increment while using Composite Primary keys in Room persistent library?

I found another way around for this problem because as per my knowledge after some R&D, we can not have auto increment property in Composite Primary keys. So I used indices and unique constraint here because Room does not have direct UNIQUE constraint till now. So below is my working code:

@Entity(tableName = "newsPapers", indices = {@Index(value = 
       {"news_paper_name"}, unique = true)})
public class SelectNewsModel {

    @PrimaryKey(autoGenerate = true)
    private int news_paper_id;

    @ColumnInfo(name = "image_url")
    private String imageUrl;

    @ColumnInfo(name = "news_paper_name")
    private String newsPaperName;
}

Android Room: How to auto generate primary id, without using autoGenerate = true (To avoid SQLite keyword AUTOINCREMENT)?

I expect we need not to assign id manually, even without using Room autoGenerate = true.

It is not necessary to use autogenerate = true, and have the same generation of values. You can use (another way is shown later):-

@PrimaryKey
@ColumnInfo(name = "id")
private Long id; /* or private Long = null; */

Noting the use of the Long object rather than the long primitive. You then set id to null for a value to be generated or have suitable constructors.

Room sees the null, in the case of PRIMARY KEY and a non-primitive integer type, and omits the column from the generated SQL.

As an example based upon your schema consider :-

@Entity
class Cities {
   @PrimaryKey
   private Long id;
   private String name;

   @Ignore
   public Cities(String name) {
      this.name = name;
   }
   @Ignore
   public Cities(Long id, String name) {
      this.id = id;
      this.name = name;
   }
   public Cities(){}

   public Long getId() {
      return id;
   }

   public void setId(Long id) {
      this.id = id;
   }

   public String getName() {
      return name;
   }

   public void setName(String name) {
      this.name = name;
   }
}

With a convenience @Insert e.g. :-

@Dao
interface CitiesDao {
    @Insert
    long insert(Cities city);
}

Alternately you could utilise an @Query to utilise an INSERT SQL that suits. e.g. the following will insert a row just given the name with a generated id. :-

@Query("INSERT INTO cities (name) VALUES(:name)")
long insert(String name);

• The important factor is including the specific column(s) without the id column.

An example of using the above (2 methods) :-

    citiesdao.insert(new Cities("San Jose"));
    citiesdao.insert(new Cities("New York"));
    citiesdao.insert(new Cities(100L,"San Francisco"));
    citiesdao.insert(new Cities("Nevada"));
    citiesdao.insert("Dallas");

• you probably would not want to use the third that inserts with a specific id (or could perhaps use onConflict = IGNORE).

The above results in (via App Inspection) :-

How do you make composite primary key with one being autogenerated?

It seems that indexes do offer the functionality I need according to the documentation and so I ended up with this:

@Entity(tableName = "Section", indices = [Index(value = ["number","numberOfServer"], unique = true)])
data class Section(
    @ColumnInfo(name = "number")
    var number: Int,

    @ColumnInfo(name = "numberOfServer")
    var numberOfServers: Int
){
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "sectionID")
    var id: Long = 0
}   

 

Thank you for participating

Creating field ID Primary Key auto increment in Room

It can be done using annotation first add the dependencies for database and annotation processor after that

  data class Destination(
    @PrimaryKey(autoGenerate = true)
    var id: Int = 0,
    var Sequence: String? = null,
    var Description: String? = null,
    var Status: String? = null
)

Migrate Room database with Autoincrement Primary Key

You are at best only saving 1 row.

Using the above you would want an Array of ContentValue's to save multiple users.

e.g.

ArrayList<ContentValues> cvlist = new ArrayList<>();

Cursor c = database.query("SELECT * FROM users");
while(c.moveToNext) {
    ContentValue cv = new ContentValue();
    cv.put("uid",c.getLong(c.getColumnIndex("uid")));
    cv.put("name", c.getString(c.getColumnIndex("name")));
    cvlist.add(cv);
}

database.execSQL("DROP TABLE IF EXISTS 'users'");
        database.execSQL("CREATE TABLE IF NOT EXISTS `users` (`uid` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                "`name` TEXT)");
for(ContentValue cv: cvlist) {
    database.insert("users", 0, cv);
}

---

However, you could simplify the above by using :-

database.execSQL("CREATE TABLE IF NOT EXISTS `temp_users` (`uid` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
            "`name` TEXT)");
database.execSQL("INSERT INTO `temp_users` SELECT * FROM `users`");
database.execSQL("ALTER TABLE `users` RENAME TO `original_users`");
database.execSQL("ALTER TABLE `temp_users` RENAME TO `users`");
database.execSQL("DROP TABLE IF EXISTS `original_users`");

This creates the new table with a different name (temp_users), copies all the rows from the original table into the new table. It then renames the original table, and then renames the new table to the actual name (so now becomes the actual tables that it used) and finally drops the renamed original table.

(you could just drop the original table instead of altering it's name, however the above is safer).

• Note the above is in-principle code, it has not been run or tested and may therefore contains some errors.

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

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;

or

@PrimaryKey
var id_column: Long?=null

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

---

Demo

Consider the following two @Entity annotated classes (with and without autoGenerate=true) :-

AutoInc:-

@Entity
data class AutoInc(
    @PrimaryKey(autoGenerate = true)
    val id: Long?=null,
    val other: String
)

NoAutoInc:-

@Entity
data class NoAutoInc(
    @PrimaryKey
    var id: Long?=null,
    var other:String
)

Room (after compiling and looking at the generated java in the class that is the same name as the @Database annotated class) has the following in the createAllTables method/function:-

    _db.execSQL("CREATE TABLE IF NOT EXISTS `AutoInc` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `other` TEXT NOT NULL)");
    _db.execSQL("CREATE TABLE IF NOT EXISTS `NoAutoInc` (`id` INTEGER, `other` TEXT NOT NULL, PRIMARY KEY(`id`))");

i.e. the only difference is the AUTOINCREMENT keyword.

Then consider the following code :-

    /* Typical  where the id will be generated */
    dao.insert(AutoInc(other = "A"))
    dao.insert(AutoInc(null,other = "B"))
    dao.insert(NoAutoInc(other ="A"))
    dao.insert(NoAutoInc(null, other = "B"))

    /* Beware */
    /* Room interprets types different ways
        here 0 is taken to be 0 as id is an Object
        if long (Java) then 0 will be generated id
        getters/setters are taken in to consideration when determining type
    * */
    dao.insert(AutoInc(0,other = "W"))
    dao.insert(NoAutoInc(0,other ="W"))

    /* Unusual */
    dao.insert(AutoInc(-100,"X"))
    dao.insert(NoAutoInc(-100,other ="X"))
    dao.insert(AutoInc(9223372036854775807,"Y")) /* The maximum value for an id */
    dao.insert(NoAutoInc(9223372036854775807,"Y")) /* The maximum value for an id */

When run then the tables (via Android Studio's App Inspection) are:-

AutInc:-

Note the Z row has not been added due to :-

E/SQLiteLog: (13) statement aborts at 4: [INSERT OR ABORT INTO `AutoInc` (`id`,`other`) VALUES (?,?)] database or disk is full

However, the disk isn't full as Disk Explorer shows:-

It's by no means full as Disk Explorer shows (and of course the subsequent step works inserting a row into the database):-

and

NoAutInc

Here the Z row has been added with a generated id based upon SQLite finding an unused value due to the highest allowable value for an id having been reached as opposed to the failure due to the disk/table full.

---

Related Topics