CODEWITHSUNDEEP
androidsqlite
Tony Ennis
Tony Ennis

Reputation: 12299

Clarification on the row_id returned by sqlite's insert statement

Ok, we see here that the SQLite insert statement returns something called row id.

When inserting, we know that SQLite likes a primary key column called ID and will auto-generate an appropriate value if the ID value being inserted is null.

The glue that's missing is confirmation that the row id returned by the SQLiteDatabase.insert() method is in fact the value that was auto-generated for the row's primary key. (This is not the case for Oracle...)

Will someone confirm or deny, please?

Resolution

Ok, so from the link posted by @sixfeetsix:

Each entry in an SQLite table has a unique 64-bit signed integer key called the "rowid". The rowid is always available as an undeclared column... If the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the rowid.

Then his auto-increment link verifies that the row_id and the auto-increment value will be the same value.

That's a wrap, thanks, folks!

Upvotes: 5

Views: 2371

Answers (2)

Chopin
Chopin

Reputation: 1452

They are two separate things.

The row_id returned by insert() is the unique identifier for the row inserted, and it's an identifier used internally by SQLite.

On the other hand, you can have a column in the table that represents your business' key, declared:

_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

So SQLite will generate values for that column for you.

As far as I see, there is no such function in SQLite like SQL Server's SCOPE_IDENTITY() (which returns the last autoincremental inserted id).

So, if you made an insert and need to retrieve the auto-generated id, you can do something like:

SELECT seq FROM sqlite_sequence WHERE name='tableName'

But take note that this is not a safe way in a concurrent scenario.

Upvotes: 2

user610650
user610650

Reputation:

The Android insert(...) method returns:

the row ID of the newly inserted row, or -1 if an error occurred

This can be confusing to the uninitiated, but the row id in sqlite is either the integer primary key field, or, in its absence, a 64-bit undeclared column named ROWID.

So when you insert either NULL or no value in an auto-increment field, the generated value is returned.

Upvotes: 4

Related Questions