CODEWITHSUNDEEP
androidandroid-sqlitesqliteopenhelper
J.Doe
J.Doe

Reputation: 187

SQLite Autoincrement not inserting

I have an SQLite Database and when I insert the ID should be automaticly incrementet with AUTOINCREMENT.

But it is always null.

This is the create table

    @Override
       public void onCreate(SQLiteDatabase db) {
       db.execSQL("CREATE TABLE ausgaben (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, TAG text,DATUM text, AUSGABE text, MENGE text, KATEGORIE text)");
    }

And this is how I insert data:

    public boolean insertAusgabe(String tag, String datum, String ausgabe, String menge, String kategorie){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.putNull("id");
    contentValues.put(AUSGABEN_TAG,tag);
    contentValues.put(AUSGABEN_DATUM,datum);
    contentValues.put(AUSGABEN_AUSGABE,ausgabe);
    contentValues.put(AUSGABEN_MENGE,menge);
    contentValues.put(AUSGABEN_KATEGORIE,kategorie);
    db.insert(TABLE_NAME,null,contentValues);
    return true;
}

If I understand right, this should work correctly. But the database looks like this: enter image description here

Upvotes: 1

Views: 1466

Answers (1)

MikeT
MikeT

Reputation: 56938

It would appear that you are expecting the id column to be null rather than a number.

If you code id INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT (see note about AUTOINCREMENT below) then that column is a special column that is an alias of the rowid column (unless the table has been defined using WITHOUT ROWID).

The rowid column cannot be null and must be a integer value. If an attempt is made to insert a row where the value for the column is null (or not specified) then SQLite will assign an integer value (long for java). 1 if there are no rows in the table then 1 greater than the highest number used.

Hence why you have a sequence of numbers in the id column.

If, for example the table were defined using id INT PRIMARY KEY NOT NULL then, the id column IS NOT an alias of the rowid column. (AUTOINCREMENT can then not be used as it can only be used for an alias of the rowid column) Then none of the inserts would work as the value for the id column would be NULL which due to the coding of the NOT NULL constraint will result in a constraint conflict.

However if the column were defined using id INT PRIMARY KEY, then null values for the id would be allowed. Noting that coding PRIMARY KEY, implies a UNIQUE constraint, that is all values must be UNIQUE. SQLite considers all NULL values as being unique in comparison to each other.

So the last definition would allow what appears to be your expected result. However, what use would an indeterminate value be for the purpose of identifiyting a row? (that's rhetorical).

As such the result you initially obtained, is the more useful result. Even if not intended.

A note on AUTOINCREMENT

AUTOINCREMENT is very likely not needed, this specifies an extension of the rowid determination algorithm in that it

  • enforces the latest rowid value being greater than any existing or used rowid,

that is it relies upon another table, namely sqlite_sequence to record the highest allocated rowid and then it uses the higher of the highest existing rowid or the value stored for the table in the sqlite_sequence table.

With AUTOINCREMENT when the highest possible value (9223372036854775807) has been assigned and an attempt is made to insert a new row. Then an SQLITE_FULL error will result. Without, attempts are made to use an random unused value (e.g. if rows have been deleted).

With AUTOINCREMENT there is an overhead (something like 8-12% according to What are the overheads of using AUTOINCREMENT for SQLite on Android?).

NOTE

It should be noted that there is no gaurantee that the rowid, with or without the AUTOINCREMENT keyword will increase by 1. There are some situations where values may be skipped as per

Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.

SQLite Autoincrement

*In short it is not wise to have any expectation of the * id/rowid column to be anything other than a means of efficiently identifying a row.

Upvotes: 2

Related Questions