# Recordset is not editable because no primary key exists **URL:** \ **Category:** Databases\ **Created:** [June 18, 2013, 3:30pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962 "2013-06-18T15:30:36Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Kurt\_Garnjost](https://forum.xojo.com/user_avatar/forum.xojo.com/kurt_garnjost/32/1577_2.png) [@Kurt\_Garnjost](https://forum.xojo.com/u/Kurt_Garnjost)\ **Post date:** [June 18, 2013, 3:30pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/1 "2013-06-18T15:30:36Z")
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: ```auto rs = db.SQLSelect("SELECT rowid, * FROM FilesToProcess") ``` At this point, I am at a loss on how to get past this. ---
**Author:** ![EDGARD\_LANDWEHRKAMP](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/3e96dc/32.png) [@EDGARD\_LANDWEHRKAMP](https://forum.xojo.com/u/EDGARD_LANDWEHRKAMP)\ **Post date:** [June 18, 2013, 4:47pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/2 "2013-06-18T16:47:52Z")
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. ---
**Author:** ![Kurt\_Garnjost](https://forum.xojo.com/user_avatar/forum.xojo.com/kurt_garnjost/32/1577_2.png) [@Kurt\_Garnjost](https://forum.xojo.com/u/Kurt_Garnjost)\ **Post date:** [June 18, 2013, 4:52pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/3 "2013-06-18T16:52:28Z")
Thanks for the reply. Well, that’s annoying. However, I will try creating my own primary key and see if that solves my problem. ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [June 18, 2013, 6:39pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/4 "2013-06-18T18:39:42Z")
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) see [http://sqlite.org/lang\_vacuum.html](http://sqlite.org/lang_vacuum.html) 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 `CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z); ` means X is NOT an alias to rowid but IS the primary key ---
**Author:** ![Tim\_Hare](https://forum.xojo.com/user_avatar/forum.xojo.com/tim_hare/32/176_2.png) [@Tim\_Hare](https://forum.xojo.com/u/Tim_Hare)\ **Post date:** [June 18, 2013, 7:30pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/5 "2013-06-18T19:30:20Z")
> [@](#): > > `rs = db.SQLSelect("SELECT rowid, * FROM FilesToProcess")` 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. ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [June 18, 2013, 7:38pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/6 "2013-06-18T19:38:27Z")
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 > [@15079:@Kurt Garnjost](#): > > At this point, I am at a loss on how to get past this. 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 ---
**Author:** ![Nick\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/76d3ee/32.png) [@Nick\_P](https://forum.xojo.com/u/Nick_P)\ **Post date:** [June 18, 2013, 7:48pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/7 "2013-06-18T19:48:26Z")
Had exactly same issue as Kurt when switching to sqlitedatabase…have reverted to Realsqldatabase and things work just fine (without primary key) ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [June 18, 2013, 8:00pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/8 "2013-06-18T20:00:21Z")
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. ---
**Author:** ![Kurt\_Garnjost](https://forum.xojo.com/user_avatar/forum.xojo.com/kurt_garnjost/32/1577_2.png) [@Kurt\_Garnjost](https://forum.xojo.com/u/Kurt_Garnjost)\ **Post date:** [June 18, 2013, 8:31pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/9 "2013-06-18T20:31:08Z")
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: ```auto CREATE Table ProcItems (PrimeKey INTEGER PRIMARY KEY ASC, ... ``` When invoke it in the select query: ```auto 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. ---
**Author:** ![Kurt\_Garnjost](https://forum.xojo.com/user_avatar/forum.xojo.com/kurt_garnjost/32/1577_2.png) [@Kurt\_Garnjost](https://forum.xojo.com/u/Kurt_Garnjost)\ **Post date:** [June 18, 2013, 8:33pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/10 "2013-06-18T20:33:32Z")
Obviously, I have mixed the two database tables in my examples. The primary key was also added to the FilesToProcess table: ```auto CREATE Table FilesToProcess (PrimeKey INTEGER PRIMARY KEY ASC, ... ``` ---
**Author:** ![Dale\_Arends](https://forum.xojo.com/user_avatar/forum.xojo.com/dale_arends/32/442_2.png) [@Dale\_Arends](https://forum.xojo.com/u/Dale_Arends)\ **Post date:** [June 18, 2013, 8:43pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/11 "2013-06-18T20:43:37Z")
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. > [@](#): > > “SQLite requires the primary key in order to do the UPDATE. RealSQLDatabase would embed the rowid for you behind the scenes, but this caused lots of problems with more complex SQL statements.” 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. ---
**Author:** ![Kurt\_Garnjost](https://forum.xojo.com/user_avatar/forum.xojo.com/kurt_garnjost/32/1577_2.png) [@Kurt\_Garnjost](https://forum.xojo.com/u/Kurt_Garnjost)\ **Post date:** [June 18, 2013, 9:05pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/12 "2013-06-18T21:05:18Z")
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. ---
**Author:** ![Tim\_Hare](https://forum.xojo.com/user_avatar/forum.xojo.com/tim_hare/32/176_2.png) [@Tim\_Hare](https://forum.xojo.com/u/Tim_Hare)\ **Post date:** [June 18, 2013, 9:49pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/13 "2013-06-18T21:49:02Z")
> [@](#): > > `rs = db.SQLSelect("SELECT PrimeKey, * FROM FilesToProcess)` You do not need to specify your primary key separately like you do rowid. It will be included in “select \*” (rowid is not). ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [June 19, 2013, 12:39am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/14 "2013-06-19T00:39:42Z")
> [@15159:@Kurt Garnjost](#): > > (PrimeKey INTEGER PRIMARY KEY ASC, … 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 `CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z); ` means X is NOT an alias to rowid but IS the primary key[/quote] ---
**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\ **Post date:** [June 19, 2013, 9:03am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/15 "2013-06-19T09:03:38Z")
Kurt: 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. ---
**Author:** ![Kurt\_Garnjost](https://forum.xojo.com/user_avatar/forum.xojo.com/kurt_garnjost/32/1577_2.png) [@Kurt\_Garnjost](https://forum.xojo.com/u/Kurt_Garnjost)\ **Post date:** [June 19, 2013, 2:02pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/16 "2013-06-19T14:02:36Z")
Norm, 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): ``` 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));" ``` So, PrimeKey should be an alias for rowid. However, when I was using: ```auto 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. ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [June 19, 2013, 2:20pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/17 "2013-06-19T14:20:15Z")
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 ---
**Author:** ![Kurt\_Garnjost](https://forum.xojo.com/user_avatar/forum.xojo.com/kurt_garnjost/32/1577_2.png) [@Kurt\_Garnjost](https://forum.xojo.com/u/Kurt_Garnjost)\ **Post date:** [June 19, 2013, 4:19pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/18 "2013-06-19T16:19:04Z")
When I was using the select statement: ```auto 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. ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [June 19, 2013, 4:47pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/19 "2013-06-19T16:47:25Z")
I wonder if the issue is that a query like rowid, \* possibly selects the rowid twice ---
**Author:** ![Tim\_Hare](https://forum.xojo.com/user_avatar/forum.xojo.com/tim_hare/32/176_2.png) [@Tim\_Hare](https://forum.xojo.com/u/Tim_Hare)\ **Post date:** [June 19, 2013, 5:02pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/20 "2013-06-19T17:02:52Z")
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. [Next page](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962.md?page=2)