Kotlin Android studio : SQlite auto increment isn't working

Issue

I have a issue where when I enter the first record into my data it goes into slot ID = 1. But if i enter another record it doesnt increment to id = 2 and tries to use ID = 1 even though i stated AUTOINCREMENT in the SQL code.

SQLite class

class SQLiteHelper(context: Context):SQLiteOpenHelper(context, DATABASE_NAME,null, DATABASE_VERSION){
    companion object{
        private const val DATABASE_VERSION = 1
        private const val DATABASE_NAME = "anki.db"
        private const val TBL_WORD = "tbl_word"
        private const val ID = "id"
        private const val DATE = "date"
        private const val ENGLISHWORD = "englishWord"
        private const val JAPANESEWORD = "jpnWord"
        private const val TIME = "time" 
        private const val ANSWER = "answer"
    }

override fun onCreate(db: SQLiteDatabase?) {
    val createTblWord = ("CREATE TABLE "+ TBL_WORD + "("
            + ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"+ DATE + " TEXT,"+ ENGLISHWORD + " TEXT,"
            + JAPANESEWORD + " TEXT,"+ TIME + " INTEGER,"+ ANSWER + " INTEGER"+")")
    db?.execSQL(createTblWord)
}

override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
    db!!.execSQL("DROP TABLE IF EXISTS $TBL_WORD")
    onCreate(db)
}

fun insertWord(word: WordModel): Long{
    val db = this.writableDatabase
    val contentValues = ContentValues()
    contentValues.put(ID,word.id)
    contentValues.put(DATE,word.date)
    contentValues.put(ENGLISHWORD,word.engword)
    contentValues.put(JAPANESEWORD,word.jpword)
    contentValues.put(TIME,word.time)
    contentValues.put(ANSWER,word.answer)

    val success = db.insert(TBL_WORD, null , contentValues)
    db.close()
    return success
}

Main class add word method

    private fun addWord(){
    val date = etDate.text.toString()
    val jpword = etJpWord.text.toString()
    val engword = etEngWord.text.toString()

    if(date.isEmpty() || jpword.isEmpty() || engword.isEmpty()){//必要な情報が入力してない場合
        Toast.makeText(this,"必要な情報を入力してください。",Toast.LENGTH_SHORT).show()
        clearEditText()
    }else{
        val word = WordModel(date=date, jpword = jpword, engword = engword)
        val status = sqliteHelper.insertWord(word)
        //Check Insert success or not success
        if(status > -2){
            Toast.makeText(this,"単語を追加しました",Toast.LENGTH_SHORT).show()
        }else{
            Toast.makeText(this,"データが保存されてないようです。",Toast.LENGTH_SHORT).show()
        }
    }
}

Shouldnt ID auto increment ?? Tried debug and Id clearly doesnt auto increment and tries to use the same id

Solution

Can you please share your WordModel? What do you use for id when create a new word in your addWord() method:

val word = WordModel(date=date, jpword = jpword, engword = engword)

Perhaps you use default id value of your WordModel like here: data class WordModel(id: Long? = 1, ...). And if it's true you can change the default value of id here to be like data class WordModel(id: Long? = null, ...) and it should work to insert auto-generated id values for db rows.

In another case you can use base SQLite BaseColumns._ID for creating your table like

"CREATE TABLE $TBL_WORD (${BaseColumns._ID} INTEGER PRIMARY KEY AUTOINCREMENT, $ENGLISHWORD TEXT, $JAPANESEWORD TEXT, etc)"

and then just don't put id like a value at all in your SQLiteHelper::insertWord(word: WordModel) method. It will be automatically generated like an auto incremented value for any new row in db.

You can see example in docs.

Answered By - Eugene V