Why is SQLite not incrementing my primary key?

Question

Here is my table,

sqlStmt = [ [ NSString stringWithFormat:@"Create Table %@  (recordNo INTEGER PRIMARY KEY AUTOINCREMENT, noOfPlayer  INTEGER, smallBlindAmt INTEGER, bigBlindAmt INTEGER , startingChips INTEGER, roundTimer INTEGER) " , SETTINGS_TABLE  ] StringUsingEncoding:NSUTF8StringEncoding ] ;

insert query,

sqlStmt = [ [ NSString stringWithFormat: @"insert into %@ values (NULL,%d, %d ,%d ,%d ,%d )" , strTableName ,noOfPlayers, SmallAmt, BigAmt, StartingChips, RoundTime ] UTF8String ] ;

get last record's record id,

lastRecordNo = sqlite3_last_insert_rowid(dbObj);

The lastRecordNo is always 0, and I am unable insert another values because it gives the error primary key must be unique.

I am unable to get the problem associated with it?

How to fetch the last record id which is primary key and autoincrement.?

Is there any problem in my insert query?

Can anyone explain me with example create, insert and select queries where primary is an autoincrement?

Answer 1

I don't really know why it doesn't work, you might be reconnecting or something. I would also suggest not looking for last row id as another process may increase it in parallel.

Answer 2

Don't explicitly set the primary key in your insert statement, let the system assign it using auto-increment.

Change the insert statement to:

insert into %@ (noOfPlayer, smallBlindAmt, bigBlindAmt, startingChips, roundTimer) values (%d, %d ,%d ,%d ,%d)