SQLite auto increment not working

Question

ok this isn't spamming and it's supposed to be simple I don't know why it's not working this is my code:

gamesdatabase = openOrCreateDatabase("GamesDatabase", MODE_PRIVATE, null);
gamesdatabase.execSQL("CREATE TABLE IF NOT EXISTS Games (ID INTEGER PRIMARY KEY, Name
VARACHAR, NPlayers INT(1), NRounds INT(2), WinScore INT(2));");

gamesdatabase.execSQL("INSERT INTO Games 
(ID, Name, NPlayers, NRounds, WinScore ) VALUES ( NULL, 'TAWLA',2,0,0 );");

gamesdatabase.execSQL("INSERT INTO Games 
(ID, Name, NPlayers, NRounds, WinScore ) VALUES ( NULL, 'DOMANA',4,0,0 );");


Cursor c = gamesdatabase.rawQuery("SELECT * FROM Games", null);
c.moveToFirst();
while (c.isAfterLast() == false) {
Log.d("BEZRA", String.valueOf(c.getInt(c.getColumnIndex("ID"))));
c.moveToNext();
}

what's wrong with this ? the log displays 0 for all records

Solution 1

The primary key for SQLite tables is called _id. It is auto incrementing, and you should not be trying to insert values into it.

gamesdatabase = openOrCreateDatabase("GamesDatabase", MODE_PRIVATE, null);
gamesdatabase.execSQL("CREATE TABLE IF NOT EXISTS Games (_id INTEGER PRIMARY KEY, Name
VARACHAR, NPlayers INT(1), NRounds INT(2), WinScore INT(2));");

gamesdatabase.execSQL("INSERT INTO Games 
(Name, NPlayers, NRounds, WinScore ) VALUES ('TAWLA',2,0,0 );");

gamesdatabase.execSQL("INSERT INTO Games 
(Name, NPlayers, NRounds, WinScore ) VALUES ('DOMANA',4,0,0 );");


Cursor c = gamesdatabase.rawQuery("SELECT * FROM Games", null);
c.moveToFirst();
while (c.isAfterLast() == false) {
Log.d("BEZRA", String.valueOf(c.getInt(c.getColumnIndex("_id"))));
c.moveToNext();
}

Solution 2

What worked for me was renaming my create type from INT to INTEGER and it started working.

From this:

CREATE TABLE IF NOT EXISTS foo (id INT PRIMARY KEY, bar INT)

to this:

CREATE TABLE IF NOT EXISTS foo (id INTEGER PRIMARY KEY, bar INT)

Solution 3

c.getColumnIndex("ID") gets the index of the column, which ID is 0 indexed column, Name is 1 etc

you want

c.getInt(c.getColumnIndex("ID"))

Solution 4

You can see http://alvinalexander.com/android/sqlite-autoincrement-serial-identity-primary-key Example CRETAE_TABLE

  CREATE TABLE salespeople (
  id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  commission_rate REAL NOT NULL
);

INSERT_DATA

 INSERT INTO salespeople VALUES (null, 'Fred', 'Flinstone', 10.0);

---OR---

INSERT INTO salespeople (first_name, last_name, commission_rate) VALUES ('Fred', 'Flinstone', 10.0);

View more solutions

Comments

[BezrA]

I did set the AUTOINCREMENT and didn't work and then I read that primary key is more than enough

[Gustek]

I wrote NOT enough. You need both primary key and auto increment.

[BezrA]

it's like that in the code it was my fault when copying it and it's not working thanks

[Patrick Evans]

@Gustek, sqlite assumes autoincrement for a field specified with PRIMARY KEY

[BezrA]

it didn't work but thanks anyway, the trick was in renaming ID into _id, AUTOINCREMENT is not necessary when you use integer primary key

[njzk2]

according to sqlite sqlite.org/autoinc.html documentation, PK is sufficient to obtain unique ids, and usually they are monotonic (unless you set yourself some strange id)

[Greener]

This answered a problem I was having with my code. Thanks!

[Julio]

Actually the column name doesn't need to be named "_id"

[Philip Sheard]

You're probably right, but you are commenting on an answer that I gave 8 years ago.

[v010dya]

For whatever reason, this is the correct answer.