# 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:** 2
**Author:** ![Paul\_Lefebvre](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_lefebvre/32/3152_2.png) [@Paul\_Lefebvre](https://forum.xojo.com/u/Paul_Lefebvre)\ **Post date:** [June 19, 2013, 6:20pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/21 "2013-06-19T18:20:44Z")
> [@15281:@Norman Palardy](#): > > I wonder if the issue is that a query like rowid, \* possibly selects the rowid twice I just tried an example using rowid, \* on a table without a primary key and I also get the “Error: RecordSet is not editable because no primary key exists.” \<[https://xojo.com/issue/27522](https://xojo.com/issue/27522)\> ---
**Author:** ![Ron\_Bower](https://forum.xojo.com/user_avatar/forum.xojo.com/ron_bower/32/4657_2.png) [@Ron\_Bower](https://forum.xojo.com/u/Ron_Bower)\ **Post date:** [July 3, 2013, 3:37pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/22 "2013-07-03T15:37:02Z")
I am having the same problem. Having just switched from Real Studio to Xojo, I was attempting to replace the Deprecated RealSQLDatabase with the new SQLiteDatabase Class. None of my RealSQL Databases had a primary key and I never used rowid. What is the quickest way to to make the new SQLiteDatabase Class compatable with the existing RealSQLDatabase files ? Ron Bower ---
**Author:** ![Paul\_Lefebvre](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_lefebvre/32/3152_2.png) [@Paul\_Lefebvre](https://forum.xojo.com/u/Paul_Lefebvre)\ **Post date:** [July 3, 2013, 4:41pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/23 "2013-07-03T16:41:54Z")
> [@18061:@Ron Bower](#): > > What is the quickest way to to make the new SQLiteDatabase Class compatable with the existing RealSQLDatabase files ? If you do not use RecordSet.Edit then you don’t have to do anything. If you do use RecordSet.Edit then you need to add a primary key. Unfortunately, due to the above bug the easiest change of doing “SELECT rowid, \* FROM MyTable” does not work in 2013r1. The bug is fixed for 2013r2, though. So right now, the quickest thing to do would be to create new tables with a primary key and then copy your data to them using INSERT INTO SELECT FROM. ---
**Author:** ![Ron\_Bower](https://forum.xojo.com/user_avatar/forum.xojo.com/ron_bower/32/4657_2.png) [@Ron\_Bower](https://forum.xojo.com/u/Ron_Bower)\ **Post date:** [July 3, 2013, 5:47pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/24 "2013-07-03T17:47:31Z")
Thanks, Paul… I believe that I’m going to continue to use the deprecated RealSQLDatabase class until, at least, some of these issues are resolved. I appreciate your response and will be monitoring this change closely. Ron Bower ---
**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:** [July 3, 2013, 6:35pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/25 "2013-07-03T18:35:24Z")
There’s nothing to resolve. If you want to use the new class, you need to adjust your table layout or your code. ---
**Author:** ![Paul\_Lefebvre](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_lefebvre/32/3152_2.png) [@Paul\_Lefebvre](https://forum.xojo.com/u/Paul_Lefebvre)\ **Post date:** [July 3, 2013, 7:16pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/26 "2013-07-03T19:16:33Z")
Oops, I left the most obvious thing off my reply: The easiest thing to do if you have tables without a defined primary key is to stick with RealSQLDatabase for the time being. ---
**Author:** ![Carl\_Hogue](https://forum.xojo.com/letter_avatar_proxy/v4/letter/c/35a633/32.png) [@Carl\_Hogue](https://forum.xojo.com/u/Carl_Hogue)\ **Post date:** [July 22, 2013, 5:35am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/27 "2013-07-22T05:35:31Z")
> [@18090:@Paul Lefebvre](#): > > Oops, I left the most obvious thing off my reply: The easiest thing to do if you have tables without a defined primary key is to stick with RealSQLDatabase for the time being. That’s what I had to do. If you update your code to replace the deprecated REALSQLdatabase with SQLiteDatabase, RecordSet.Update won’t work without a Primary Key and it doesn’t appear to be possible to add a Primary Key to an existing table that lacks one in SQLiteDatabase. ---
**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:** [July 22, 2013, 10:35am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/28 "2013-07-22T10:35:26Z")
Did you try EXPLAIN ? (SQLite EXPLAIN) ---
**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:** [July 22, 2013, 5:37pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/29 "2013-07-22T17:37:29Z")
> [@21525:@Carl Hogue](#): > > That’s what I had to do. If you update your code to replace the deprecated REALSQLdatabase with SQLiteDatabase, RecordSet.Update won’t work without a Primary Key and it doesn’t appear to be possible to add a Primary Key to an existing table that lacks one in SQLiteDatabase. In sqlite your table has one - ALWAYS - you may just have to explicitly select the ROWID instead of just selecting \* ---
**Author:** ![Carl\_Hogue](https://forum.xojo.com/letter_avatar_proxy/v4/letter/c/35a633/32.png) [@Carl\_Hogue](https://forum.xojo.com/u/Carl_Hogue)\ **Post date:** [July 23, 2013, 12:37am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/30 "2013-07-23T00:37:37Z")
> [@21640:@Norman Palardy](#): > > In sqlite your table has one - ALWAYS - you may just have to explicitly select the ROWID instead of just selecting \* I tried using rowid but I kept getting the no primary key error. I also tried to add a primary key column but got an error message saying that I couldn’t add a primary key to the table. It is possible to add a primary key to a table that doesn’t have one in MySQL, but that does not appear to be possible in SQLite. All of the tables my app creates have a primary key except for one, and that is the user settings table that has only one record set, which is why I didn’t specify a primary key for that one. ---
**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:** [July 23, 2013, 1:09am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/31 "2013-07-23T01:09:05Z")
> [@21723:@Carl Hogue](#): > > I tried using rowid but I kept getting the no primary key error. I also tried to add a primary key column but got an error message saying that I couldn’t add a primary key to the table. It is possible to add a primary key to a table that doesn’t have one in MySQL, but that does not appear to be possible in SQLite. All of the tables my app creates have a primary key except for one, and that is the user settings table that has only one record set, which is why I didn’t specify a primary key for that one. Doesn’t matter whether you did specify one when you created the table or not. I’d urge you to but sqlite adds one if you didn’t specify one. Which version of Xojo ? What does your sql look like ? ---
**Author:** ![Carl\_Hogue](https://forum.xojo.com/letter_avatar_proxy/v4/letter/c/35a633/32.png) [@Carl\_Hogue](https://forum.xojo.com/u/Carl_Hogue)\ **Post date:** [July 23, 2013, 3:50am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/32 "2013-07-23T03:50:32Z")
[quote=21724:@Norman Palardy]Doesn’t matter whether you did specify one when you created the table or not. I’d urge you to but sqlite adds one if you didn’t specify one. Which version of Xojo ? What does your sql look like ?[/quote] Xojo version is 2013r2. SQL looks like this: ```auto RS = DB.SQLSelect("SELECT * FROM SETTINGS WHERE ROWID='1'") ``` As far as I can tell, the records are being selected as I can read them, the problem occurs with RS.UPDATE, which results in the no primary key error. ---
**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:** [July 23, 2013, 4:33am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/33 "2013-07-23T04:33:38Z")
Try RS = DB.SQLSelect(“SELECT ROWID, \* FROM SETTINGS WHERE ROWID=‘1’”) If you did not explicitly add the integer primary key column then “select \*” wont grab it But you CAN get it by including it in the select statement (and you _should_ list the columns explicitly) ---
**Author:** ![Ron\_Bower](https://forum.xojo.com/user_avatar/forum.xojo.com/ron_bower/32/4657_2.png) [@Ron\_Bower](https://forum.xojo.com/u/Ron_Bower)\ **Post date:** [July 24, 2013, 4:39pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/34 "2013-07-24T16:39:24Z")
I believe that I have successfully upgraded from the RealSQLDatabase class to the new SQLiteDatabase class. Current IDE: Xojo 2013r2 I think I can now go back and remove some unnecessary or redundant code but wanted to be consistent as possible and do complete regression testing of all operations that affected the database. Under Real Studio, I had a couple RealSQLDatabase class databases for which I never defined a Primary Key as I did not see the need in my application. The large majority of the queries were of the format “SELECT \* FROM…” and I had only one place where I actually performed a db.Edit method call. Upgrading to the SQLiteDatabase class involved making the following changes: 1. Redefining every instance of the RealSQLDatabase class to be a SQLiteDatabase class. 2. Changing every db.SQLSelect( “SELECT \* FROM…” ) to be db.SQLSelect( “SELECT ROWID, \* FROM…” ). 3. Making sure that every db.Commit statement was preceded by a db.SQLExecute( “BEGIN TRANSACTION” ). The statement was placed before any loops were entered or before the first non-loop change to a database. So far, everything I’ve tested seems to be working just fine - no problems have been detected that I can see. So, first question… Do you see anything that I missed ? Second question, just to make sure I understand some things: 1. I assume I can eliminate the “BEGIN TRANSACTION” and Commit commands for one-at-a-time updates and only leave it for multiple updates in a loop. Correct ? 2. I assume that the ONLY place where I really need the “SELECT ROWID, \* FROM …” in place of the “SELECT \* FROM …” is where I perform the db.Edit. Correct ? Thanks for all you guys do for all of us. ---
**Author:** ![Paul\_Lefebvre](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_lefebvre/32/3152_2.png) [@Paul\_Lefebvre](https://forum.xojo.com/u/Paul_Lefebvre)\ **Post date:** [July 24, 2013, 5:02pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/35 "2013-07-24T17:02:22Z")
> [@22227:@Ron Bower](#): > > So, first question… Do you see anything that I missed ? No. Looks good to me. [quote=22227:@Ron Bower]Second question, just to make sure I understand some things: 1. I assume I can eliminate the “BEGIN TRANSACTION” and Commit commands for one-at-a-time updates and only leave it for multiple updates in a loop. Correct ?[/quote] Correct. [quote=22227:@Ron Bower] 2. I assume that the ONLY place where I really need the “SELECT ROWID, \* FROM …” in place of the “SELECT \* FROM …” is where I perform the db.Edit. Correct ?[/quote] Correct. ---
**Author:** ![Ron\_Bower](https://forum.xojo.com/user_avatar/forum.xojo.com/ron_bower/32/4657_2.png) [@Ron\_Bower](https://forum.xojo.com/u/Ron_Bower)\ **Post date:** [July 24, 2013, 5:10pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/36 "2013-07-24T17:10:03Z")
Thank you very much for the quick feedback, Paul. Now I’m ready to start adding some requested enhancements to the existing application. ---
**Author:** ![Carl\_Hogue](https://forum.xojo.com/letter_avatar_proxy/v4/letter/c/35a633/32.png) [@Carl\_Hogue](https://forum.xojo.com/u/Carl_Hogue)\ **Post date:** [July 25, 2013, 4:18am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/37 "2013-07-25T04:18:17Z")
Interestingly enough, this did work for the table that was created without a primary key: ```auto DB.SQLSelect("SELECT ROWID,* FROM SETTINGS") ``` However, this resulted in a no primary key error on a table with a primary key: ```auto DB.SQLSelect("SELECT ThisColumn FROM SETTINGS WHERE PRIMARY KEY='1'") ``` And this resulted in a type mismatch error: ```auto DB.SQLSelect("SELECT PrimaryKey,ThisColumn FROM SETTINGS WHERE PRIMARY KEY='1'") ``` As did this: ```auto DB.SQLSelect("SELECT ROWID,ThisColumn FROM SETTINGS WHERE PRIMARY KEY='1'") ``` I should also point out that the language reference uses this as an example for SQLiteDatabase: ```auto DB.SQLSelect("SELECT * FROM SomeTable") ``` ---
**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:** [July 25, 2013, 4:36am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/38 "2013-07-25T04:36:20Z")
[quote=22414:@Carl Hogue] this resulted in a no primary key error on a table with a primary key: ```auto DB.SQLSelect("SELECT ThisColumn FROM SETTINGS WHERE PRIMARY KEY='1'") ``` [/quote] Because your recordset does not contain the primary key column. [quote]And this resulted in a type mismatch error: ```auto DB.SQLSelect("SELECT PrimaryKey,ThisColumn FROM SETTINGS WHERE PRIMARY KEY='1'") ``` [/quote] Not on thise line, probably. Where did the type mismatch show up? ---
**Author:** ![Ron\_Bower](https://forum.xojo.com/user_avatar/forum.xojo.com/ron_bower/32/4657_2.png) [@Ron\_Bower](https://forum.xojo.com/u/Ron_Bower)\ **Post date:** [July 25, 2013, 11:43am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/39 "2013-07-25T11:43:30Z")
Very new at this, so need to ask and learn… As I understand it, Primary Key is always an Integer. So would you put single quotes around the number being matched ? ---
**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:** [July 25, 2013, 4:44pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/40 "2013-07-25T16:44:12Z")
Quotes around integer values are optional. [Previous page](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962.md?page=1) [Next page](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962.md?page=3)