Android SQLite auto increment
Make it INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
. Here's what the docs say:
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then... the ROWID chosen
for the new row is at least one larger than the largest ROWID that has
ever before existed in that same table.The behavior implemented by the AUTOINCREMENT keyword is subtly
different from the default behavior. With AUTOINCREMENT, rows with
automatically selected ROWIDs are guaranteed to have ROWIDs that have
never been used before by the same table in the same database. And the
automatically generated ROWIDs are guaranteed to be monotonically
increasing.
How to make AUTO_INCREMENT on Android SQLite database?
You don't have to parse anything. If the column was created as AUTOINCREMENT
, just pass the other values:
db.execSQL("insert into "
+ TABLE_NAME
+ "(contact_id, contact_name, number_type, contact_number, duration, date, current_time, cont) "
+ "values( "+ cId + ", " + cName + ", " + numType + ", "
+ cNum + ", " + dur + ", " + date + ", " + currTime + ", ? )");
By the way, it's always recommended to insert data using the Android's insert
method:
ContentValues values = new ContentValues();
values.put("contact_id", cId);
values.put("contact_name", cName);
// etc.
db.insert(TABLE_NAME, null, values);
SQLite id auto-increment
SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table.You do not need ID1
.
See reference here
Please use this:
db.execSQL("create table " + TABLE__WORK + " (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,Name TEXT, Title TEXT, Time TEXT, Date TEXT)");
Sqlite query in Android for auto increment and default values
This should be the correct syntax:
CREATE TABLE table_name (Id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, description TEXT NOT NULL, date DATE NOT NULL, status INTEGER DEFAULT 0)
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
No Resource Found That Matches The Given Name '@Style/ Theme.Holo.Light.Darkactionbar'
Backup/Restore Sqlite Db in Android
How to Access Resource with Dynamic Name in My Case
Android Sqlitedatabase Query with Like
How to Import an Existing Project from Github into Android Studio
Writing Some Characters Like '<' in an Xml File
How to Set The App Icon as The Notification Icon in The Notification Drawer
How to Overlap Items in Linearlayoutmanager - Recyclerview (Like Stacking Cards)
How to Get a View from an Event Coordinates in Android
Set Visibility of Progress Bar Gone on Completion of Image Loading Using Glide Library
Remove All Items from Recyclerview
Retrofit with Rxjava Handling Network Exceptions Globally
Get Context of Test Project in Android Junit Test Case
How to Get a Button's Height to Match Another Element's Height
What Is Better: @Suppresslint or @Targetapi
How to Clear The Android Stack of Activities
How to Check Which Current Image Resource Is Attached to Imageview in Android Xml