Menu
I have an sqlite database on android created like this:
sqlite> .schema
CREATE TABLE criterion ('_id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active text, important text, sort int, summary text, user text, category_id int, entrytype int);
The only way I can insert a record into this table is by specifying a value for _id which I want to auto-increment. This is the only way I can get the insert working:
recid = totalrecs + 1;
String q = "insert into criterion (_id, active, important, sort, summary, user, category_id, entrytype) values (" + recid + ", \"1\", \"0\", 99, \"foobar\", \"1\", 99, 0)";
Log.d (TAG, "query:" + q);
mDb.execSQL (q);
If I leave the _id column out and don't specify a value for _id, I get an error:
android.database.sqlite.SQLiteConstraintException: criterion._id may not be NULL: insert into criterion(active, important, sort, summary, user, category_id, entrytype) values ("1", "0", 99, "foobar", "1", 99, 0)
How do I arrange the query (or schema) so Android will take care of incrementing the _id column?
Update/fixed 2 lines above (removed NOT NULL, removed _id from query):
CREATE TABLE criterion ('_id' INTEGER PRIMARY KEY AUTOINCREMENT, active text, important text, sort int, summary text, user text, category_id int, entrytype int);
String q = "insert into criterion (active, important, sort, summary, user, category_id, entrytype) values (\"1\", \"0\", 99, \"foobar\", \"1\", 99, 0)";
322
SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table. We can auto increment a field value by using AUTOINCREMENT keyword when creating a table with specific column name to auto increment. The keyword AUTOINCREMENT can be used with INTEGER field only.
Here's the syntax of ALTER TABLE statement, ALTER TABLE table_name MODIFY column_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY; In the above statement, you need to specify the table_name and column_name. Here's the SQL statement to add AUTO INCREMENT constraint to id column.
You can insert into an auto-increment column and specify a value. This is fine; it simply overrides the auto-increment generator. If you try to insert a value of NULL or 0 or DEFAULT , or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator.
In SQLite, an AUTOINCREMENT column is one that uses an automatically incremented value for each row that's inserted into the table. There are a couple of ways you can create an AUTOINCREMENT column: You can create it implicitly when you define the column as INTEGER PRIMARY KEY .
Remove the NOT NULL from the schema and you're golden.
Clarifying: Specifying NOT NULL on an auto-increment column makes it so the auto-increment doesn't function. The NOT NULL is what is making it so you have to specify the _id.
Once you remove it, and don't include the _id in the insert statement, SQL will receive the _id as NULL and automatically handle setting the _id for you.
60
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
