Recordset is not editable because no primary key exists

Having switched to Xojo and from REALSQLDatabase to SQLiteDatebase, my small background database is not working properly. The database is created and the initial insert works fine, but I use a recordset to periodically update it and this is not working. I am getting the “Recordset is not editable because no primary key exists” error when I call rs.edit, despite including rowid in my select statement:

rs = db.SQLSelect("SELECT rowid, * FROM FilesToProcess")

At this point, I am at a loss on how to get past this.

Did you have a separate Column as ID ? Please see reference below:

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.

Thanks for the reply.

Well, that’s annoying.

However, I will try creating my own primary key and see if that solves my problem.

Rowid should always exist on every sqlite table - whether you create it or not
There is a downside to NOT creating your own (they can get reset / altered when a vacuum is invoked)

However its possible to define an integer primary key that is NOT aliased to rowid
That could be the issue here
If you defined your table as

CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC)); the in these 3 cases referring to X and ROWID is equivalent

However doing

means X is NOT an alias to rowid but IS the primary key

The responses so far ignore the fact that this should have worked, even in the absence of a primary key. Especially if it worked in REALSQLdatabase and the only difference is the automatic inclusion of rowid. There must be something else going on that you haven’t mentioned.

I’m suspicious about rowid NOT being the primary key which you can do (see my post)

It’s about the only way I can think of to not have the rowid as primary key

What OS are you on ?
If you’re on OS X you can actually open your database using the command line sqlite tool & dump out the create statements for your tables with select * from sqlite_master where type=‘table’
That’d be useful to see whats up with this table

Had exactly same issue as Kurt when switching to sqlitedatabase…have reverted to Realsqldatabase and things work just fine (without primary key)

It’ll be fine with simple queries.
Complex queries (ones using intersections, unions etc) you’ll have issues.
It’s why we altered the plugin in the way we did & made it your responsibility to select out the primary key instead of trying to add it automagically.

rowid as used in my original select statement was directed at the automatically created rowid. I was not creating my own. I looked at the created database with SQLiteManage and rowid was in the database. So, I am still at a loss as to why the select statement did not work.

I added at primary key in the table create statement:


When invoke it in the select query:

rs = db.SQLSelect("SELECT PrimeKey, * FROM FilesToProcess)

I am able to edit and update the record set and database.

So, the fix is not too hard, once you understand what it is, but the failure of the original select statement, which should work, is still unexplained.

Obviously, I have mixed the two database tables in my examples. The primary key was also added to the FilesToProcess table:

CREATE Table FilesToProcess (PrimeKey INTEGER PRIMARY KEY ASC, ...

I don’t know how many threads this issue is discussed in but a posting from Paul Lefebvre (of Xojo, Inc. fame) explained it to me when I encountered this. The problem is not with the select statement but shows up with the rs.edit when there is no primary key.

Xojo no longer does this for you so you need to have the a primary key in the table and in the select statement. Yes, rowid probably will work, too.

For me SQLite was creating a rowid in the background and I was invoking it in the select statement creating the recordset, but Xojo was still insisting there was no primary key at the point of editing. When I created a separate interger primary key and invoked it in the select statement, the edit statement worked.

You do not need to specify your primary key separately like you do rowid. It will be included in “select *” (rowid is not).

There’s the culprit !
PrimeKey is NOT an alias to rowid or vice versa
So in your table rowid is NOT a primary key so you cannot edit it if you ONLY select the rowid

For PRIMEKEY to be an alias to rowid & vice versa the clause needs to be EXACTLY INTEGER PRIMARY KEY

The ASC wrecks that
See my earlier post where I mentioned this

[quote=15127:@Norman Palardy]Rowid should always exist on every sqlite table - whether you create it or not
However doing

means X is NOT an alias to rowid but IS the primary key[/quote]

did you have ““Import from Text”” and “Export to Text” in your project ?

If so, export your db contents, follow prior advices, rebuild your project, create new db and import your data.

Your brain will do not fear for data loss and concentrate on problem solve.


Actually, the SQLite documentation says:

"This means that the following three table declarations all cause the column “x” to be an alias for the rowid (an integer primary key):


So, PrimeKey should be an alias for rowid.

However, when I was using:

rs = db.SQLSelect("SELECT rowid, * FROM FilesToProcess")

There was no separate primary key set. I was depending solely on rowid as the primary key, which everything above and in the documentation tells me should work, but it did not.

I’d try making PRIMEKEY just be defined as INTEGER PRIMARY KEY and see
ROWID not being an alias for PRIMEKEY (and vice versa) is the only reason I can think of

When I was using the select statement:

rs = db.SQLSelect("SELECT rowid, * FROM FilesToProcess")

PRIMEKEY was not a field in the database at all. There was no alias to ROWID. ROWID was present in the database. I could see it using SQLiteManager. It should have been the primary key, but the recordset was not editable. It is my impression that theoretically a recordset called with the above statement should be editable when there is no other primary key in the database. However, that was not the case for me.

As noted above, there is a relatively simple work around for this, but it also appears there is a bug somewhere.

I wonder if the issue is that a query like rowid, * possibly selects the rowid twice

At this point, we’re all just speculating. Can you recreate your original table in a new database file with no primary key and verify whether you can or cannot edit a record using "select rowid, * … "? That will help narrow down the problem. It will also produce a sample that can be included in a feedback case, if necessary.