Creating Primary Key On Existing SQLite Table

Databases
1

Hi,

I have an existing Sqlite table that does not have a Primary Key. In order to properly update the table, I need to add a Primary Key. I could certainly do that easily by just using a SLQlite Management app, but I need my customers to be able to update their databases so I have to do it in code.

I can make a column by using this code:

Source.SQLExecute ("ALTER TABLE Questions ADD COLUMN id2 INTEGER") Source.Commit

That works for adding the Integer Column, But if I want that to be the Primary Key I try using the code:

Source.SQLExecute ("ALTER TABLE Questions ADD COLUMN id2 INTEGER PRIMARY KEY") Source.Commit

That code does not create the new column at all.

Does anyone know how I can add a new column and make it the primary key for that table?

Any help would be appreciated.

2

Why not use RowID?

3

I thought when you used a Database file as SQLiteDatabase you need a Primary Key. That was okay when you used RealSQLDatabase. Records would update then. But it does not work with SQLiteDatabase.

For instance if I want to copy the records from one database to another I use this code

[code] Dim sql As String
sql=“SELECT * FROM Terms”
rs=Source.SQLSelect(sql)
rs.MoveFirst

//Copies the questions
While Not rs.eof

Dim dr as new DatabaseRecord
dr=New DatabaseRecord

dr.Column("terms")=(rs.field("terms").StringValue)
dr.Column("def")=(rs.field("def").StringValue)
dr.Column("chapter")=(rs.field("chapter").StringValue)

Target.InsertRecord ("Terms",dr)
Target.Commit
rs.MoveNext

wend[/code]

If you have a Primary Key the code will copy all the records. If you don’t have a primary key, only one record gets copied. Then it stops. That why I need to add the Primary Key to the Table

4

if you don’t create a primary key, you should have an implicit RowID.

Maybe you make a select with RowID?

sql=“SELECT RowID,* FROM Terms”

5

http://sqlitebrowser.org may help you visualize what’s going on in your database.
It also shows you the queries it uses which is incredibly helpful when altering tables.

6

you can also edit the sqlmaster table
first allow to write it with

SQLExecute("PRAGMA writable_schema=1")
then edit the sqlmaster table the way you want
then stop allowing the write

SQLExecute("PRAGMA writable_schema=0")

after that it should be better to close and reopen the database, but it still works without it.

7

The Line
sql=“SELECT RowID,* FROM Terms”

Still only returns 1 record. Then it stops.

9

much faster to copy table this way

INSERT INTO table2 SELECT * FROM table1;

will RowID remain the same? most likely not, as the order of retrieval is never guaranteed

and a Primary Key is NEVER “required”… but usually “desired”

10

Are you absolutely certain that the table actually contains more than one row? Your code would indicate otherwise. Ie., “if your code is correct, and you expect it to return more than one row, but it does not, then your expectation was incorrect.” Also, if your code works correctly (meets your expectation) on one database/table, but does not on another database/table, then your expectation was wrong about the second table.

The presence or absence of a primary key would have no impact on the number of rows your code returns.

And, to answer your initial question: you cannot add a primary key to an existing table in sqlite. You must create a new table and copy the data to it.

As for your second issue: the older versions of the sqlite plugins automatically included RowID in the result set. The new one does not, so if you want to be able to edit the recordset, you must explicitly include RowID in your select statement, as Christian demonstrated. “Select *” does not return RowID. You must ask for it by name: “Select RowID, *”.

11

Hi Tim,

Thanks for taking the time to help. There is more than one record in the Table. In fact there are over 800 records. As the Table is set up now, id is a VarChar. When I run the code above I get one record only copied to the other database file.

If I manually go in and change id to a Primary Key, the same code works perfectly. All 800 or so records are copied to the second database.

As for Christian’s code

sql=“SELECT RowID,* FROM Terms” only returns one record, as the other code does.

Maybe Dave’s idea will work. If I create a new Table with a Primary Key and copy all the records to the new Table, I can then export the records of the second table to the target database file.

I could also write this app in an old Real Basic version and everything would work.

12

Don’t do this… all that will do is postpone the inevitable

13

Ok. There is one more explanation. And that would be that with id the way it is, since you are not inserting id into the new table, if there is a implicit or explicit Unique setting on id, the first insert will succeed (with a blank id) and all the rest will fail, because they have the same Id value. Try copying it with the id field.

    dr.Column("id") = rs.field("id").StringValue
    dr.Column("terms")=(rs.field("terms").StringValue)
    dr.Column("def")=(rs.field("def").StringValue)
    dr.Column("chapter")=(rs.field("chapter").StringValue)
    
    Target.InsertRecord ("Terms",dr)
14

Dave, I know what you mean, but this app was just a one time use for my customers to update their old databases too the new format, so they would only have used it once. The new software uses the newest version of Xojo.

[quote]Ok. There is one more explanation. And that would be that with id the way it is, since you are not inserting id into the new table, if there is a implicit or explicit Unique setting on id, the first insert will succeed (with a blank id) and all the rest will fail, because they have the same Id value. Try copying it with the id field.
[/quote]

Tim, that did not seem to make a difference, but you did point something out that I did not notice before. Although the ID field was present in the Table, that field had no values stored, so since the id was empty, it stopped after the first record. So I basically wrote some code that assigned unique values to all the id fields, and now it works like its suppose to work.

Thanks so much for your help. I really appreciate it!! :slight_smile: