Every row of every SQLite table has a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the “rowid”. The rowid value can be accessed using one of the special case-independent names “rowid”, “oid”, or “rowid” in place of a column name. If a table contains a user defined column named “rowid”, “oid” or “rowid”, then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.[/i]
I read this as if I define a rowid and retrieve this column it is NOT referring to the unique rowid column generated by the database engine. If this is true, I am wondering why the RealSQLiteDatabase ever needed to automatically insert this into the table upon creation?
So, when a database table is created with code should the rowid parameter with a UNIQUE PRIMARY KEY be created? Or, are we duplicating efforts here and SQLite creates this regardless? If I create a rowid column then refer to it am I referring to the column I created OR the column inherent to the column created by the DB engine.
I guess, this is the answer from the User Guide - Framework:
Auto-Incrementing Primary Keys
With SQLite, if a table has a single column specified as the
INTEGER primary key, then that column auto-increments when a
row is added to the table. This column is said to map to the
internal rowid column that is on all SQLite tables.
However, just because SQLite has an internal rowid column, you
should not rely on it as your primary key. Rowid values can be
changed behind the scenes by SQLite and this could possibly
corrupt any relationships in your database. Always create a
separate primary key for your tables.
After updating my source code to use SQLiteDatabase instead of REALSQLdatabase I still haven’t quite figured out what the deal is with rowid. RecordSet.Edit/RecordSet.Update doesn’t work reliably if rowid is not included in the Select statement even if the Select statement does reference a primary key.
Neither of these work:
Select SomeRecord from SomeTable where PrimaryKey=SomeValue
Select PrimaryKey,SomeRecord from SomeTable where PrimaryKey=SomeValue
Only this works reliably:
Select rowid,SomeRecord from SomeTable where PrimaryKey=SomeValue