Android Question SQLite ID INTEGER PRIMARY KEY AUTOINCREMENT Insert

[Mahares]

This is somewhat triggered by another thread very recently created by a member where thanks to too many answers became a little muddled. So, I created this one fresh for an independent table not joined to another table:

Although a NULL or a number (not used previously) can be inserted in ID column, I like to go further with this:

1. Do you agree that if a number (not used previously as ID and became available because of a row deletion) can be accepted by the INSERT query. Example: 845 and the last ID is: 874
2. Do you agree that if a number (not used previously as ID) and enclosed in double quotes can be accepted by the INSERT query. Example: “901”.
3. Do you agree that if you are inserting new records and you are listing some or all the column names in the INSERT query, you can leave out the ID and it will automatically be filled with the incremented number.

I am really interested only in any detailed answers if you only disagree with any of the above statements.

 

[Erel]

Worth reading:

SQLite Autoincrement

#AdditionalJar: sqlite-jdbc-3.7.2
Sub Process_Globals
  
End Sub

Sub AppStart (Args() As String)
    Log("Hello world!!!")
    Dim sql As SQL
    File.Delete(File.DirApp, "1.db")
    sql.InitializeSQLite(File.DirApp, "1.db", True)
    sql.ExecNonQuery("CREATE TABLE table1 (col1 INTEGER PRIMARY KEY  AUTOINCREMENT , col2 TEXT)")
    sql.ExecNonQuery2("INSERT INTO table1 (col2) VALUES (?)", Array("test"))
    sql.ExecNonQuery2("INSERT INTO table1 (col2) VALUES (?)", Array("test"))
    sql.ExecNonQuery2("INSERT INTO table1 (col2) VALUES (?)", Array("test"))
    sql.ExecNonQuery2("INSERT INTO table1 (col1, col2) VALUES (?, ?)", Array(100, "test"))
    sql.ExecNonQuery2("INSERT INTO table1 (col2) VALUES (?)", Array("test"))
    Log(sql.ExecQuerySingleResult("SELECT last_insert_rowid()")) '101
    sql.ExecNonQuery("DELETE FROM table1 WHERE col1 = 2")
    sql.ExecNonQuery2("INSERT INTO table1 (col1, col2) VALUES (?, ?)", Array(2, "test"))
    Log(sql.ExecQuerySingleResult("SELECT last_insert_rowid()")) '2
    sql.ExecNonQuery2("INSERT INTO table1 (col2) VALUES (?)", Array("test"))
    Log(sql.ExecQuerySingleResult("SELECT last_insert_rowid()")) '102
End Sub

1. Yes.
2. Not tried it. Will probably work. Don't see any reason to do it.
3. Yes.

 

[Mahares]

2. Not tried it. Will probably work. Don't see any reason to do it.

Nice concise example Erel. I have a B4A SQLite test database with few columns and the ID I tried them all and confirm that #2 works too, but I agree there is no reason to enclose it in double quotes. You were well prepared.