"how to create a integer id pk to replace varchar primary key."

[goedible]

Have a database with a varchar primary key. skyandrews said that it will load faster if I create an integer primary key instead. There are several tables relating to the varchar primary key. How can I edit all of the tables to make the database run faster?
here is the database I’m working on:
https://www.dropbox.com/scl/fo/lx6g7fq5vkuy5ehvhp4p7/AAGM7I5_ZKCmlbAKSacBlig?rlkey=dw9w58w8fd0t1fjtfh9tdnn76&st=iz7mhl4q&dl=0

[fpy]

Change existing DB primary key to autovalue

IMHO, it is impossible to fix a Firebird table where the primary key or its auto-ID property is missing. You’ve got to create a new table from scratch and copy any data into the new table. It may be necessary to remove all references to the old table and recreate them for the new one before you can delete the old table. With HSQL you can not set auto-ID in the table design window, but in the SQL window ALTER TABLE TBL ALTER COLUMN ID INT IDENTITY does the trick. Yes, it’s a pitty that Base ask…

[goedible]

These are instructions from Profile - skyandrews - Ask LibreOffice. as posted here: Table refresh on button push - #9 by goedible

Blockquote
1 Delete Relationships. Open the odb file from the original database folder, goto Tools / Relationships, delete all relations between the old Primary Key (oldPK) of the parent table (pTable) and all related old Foreign Keys (oldFK) of child tables (cTable). Save and close Relations! Save odb!
2 Create copy of pTable. In the LO Base Tables list, drag and drop pTable in an empty area below the Tables list. In the prompt, name like “pTable2”, select only the “Definition and data” option, then Create. Do Not create new primary key. Verify the new pTable2 appears in the Tables list and then open to verify it is properly populate. If it appears correct, close the table and save the odb!
3 Delete all pTable rows of data. Open pTable, select all rows (left click upper left corner header “block”), then right click the corner block and select “Delete rows”. The deletion may require some time, depending on the amount of data. When completed, close the empty pTable. Save odb, close the LO app, and then reopen the odb! (This clears LO cache in order to accomodate the following procedures adequately, preventing crashes)
4 Edit pTable. Select and edit the pTable to remove the Primary Key designation of the current primary field (oldPK) by right clicking its row marker, then deselect “Primary Key”. (The key icon should disappear) Save table! Then append an integer type field named like “ID”. Save and close the table! Save and close the odb! To simplify and avoid confusion, try giving primary key fields a name like “ID” (standard Caps), and related foreign keys like “ptable_id” (descriptive lowercase).
5 Edit “ID” field properties in db script file. In the original db folder, open the “mydb.script” file in a reliable text editor (notepad, notepad++, etc…). With editor’s “Word Wrap” OFF, find the line which begins with something like this: CREATE CACHED TABLE “pTable” - Select the line and then switch the editor’s “Word Wrap” to ON. Near the end of the selection is a segment that should be like this -
“ID” INTEGER - Carefully replace with -
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY - Save and close script!
6 Append data into pTable from pTable2. Reopen the odb, then in the Tables list, drag and drop pTable2 directly onto pTable. In the prompt, name = “pTable”, select only the “Append data” option, then Create. Do Not create new primary key! After process completes, verify pTable now has complete data, including a valid “ID” primary key auto-increment field at the last column. Close table.
7 Delete pTable2 (optional). The pTable2 may now be deleted, or keep for backup. Upon deletion, save the odb! (Recommend delete to optimize odb)
8 Create new Foreign Key fields (ptable_id) in all related child tables. Edit each child table which is related to pTable and append an integer field named like “ptable_id”. Save and close child table! Then open the child table and move it to the side to remain in view. In the LO main menu bar, select Tools / SQL. Type, or copy and paste the following into the SQL command window -
UPDATE "cTable" SET "ptable_id" = SELECT "ID" FROM "pTable" WHERE "cTable"."oldFK"= "pTable"."oldPK"
(Replace table and field names with the applicable names in your own database) Execute! If there is an error, it will be indicated in the Status window. (The command will fail, stop) The command may take a few minutes, based upon the amount of data to query. Successful completion will be indicated in the Status window. Upon successful completion, refresh the previously opened cTable by clicking the table’s menu bar “Refresh” icon. (May need to select “Refresh”) Visually verify that the new foreign key field (ptable_id) was successfully populated. If so, save and close table! Before opening the next child table, the SQL command tool must be closed! Close the SQL tool and then open the next related child table and move to the side. Then reopen the SQL tool and repeat this step for each of the other related child tables! (To clear its cache, it is recommended the LO app be saved, then completely closed and reopened between each successful SQL command update)
9 Recreate relations between new pTable.ID primary key and all related cTables containing the new ptable_id foreign key. Goto LO main menu bar Tools / Relationships and recreate relations as previously described. With each relation, edit the Update property to “Update cascade” (right click the connecting line and select Edit). This will maintain the relationship even in the event the primary key integer is changed. Save and close relationship tool! Save the odb!
10 Cleanup and debug. Before deleting all of the unneeded oldPK and oldFK fields, they may be used to debug any conversion problems which may arrise while implementing the new PK and FK fields. Otherwise, the old fields should be deleted.

[goedible]

I tried:

UPDATE "plants” SET “id” = SELECT “ID” FROM “species” WHERE “plants”.“latin_name” = “species”.“Latin_name”

I got:
1: unknown token: at ./connectivity/source/drivers/jdbc/Object.cxx:173

it’s a problem with the quotes. This worked:

UPDATE "plants" SET "id" = SELECT "ID" FROM "species" WHERE "plants"."latin_name" = "species"."Latin_name"

UPDATE "cTable" SET "ptable_id" = SELECT "ID" FROM "pTable" WHERE "cTable"."oldFK" = "pTable"."oldPK"

[skyandrews]

Yes. I apologize for using smart quotes for the instructions. When copying from smart quotes into sql, all typographic quote marks must be edited. Also a child table’s foreign key should be named like “species_id”, not just “id”. A foreign key should be somewhat descriptive about which table holds the related “ID” primary key… hence “species_id” or “speciesID”.

• Here is an embedded version of the tables showing the new Integer primary and foreign keys, created in the split version using the 10 step suggestion.
  EmbedLANdemo.odb (179.8 KB)

[Wanderer]

@skyandrews

When copying from a block quote into sql, all quotes must be manually edited.

Using three backticks ` at the beginning and end of your block instead will give a verbatim copy of your spelling

No "change" here for 'quotes'

But I was told the autometed formatting is a feature…

Can typographic quotes be switched off for ask.libreoffice.org?

I saw some code on this site, where typographic quotes replaced the original quotes. Example far down in this question: It contains the following block: SELECT * FROM “tArtist_Song” ORDER BY ( SELECT “Song_Title” FROM “tSongs” WHERE “ID” = “tArtist_Song”.“SongID” ) As this usually breaks the code, you can’t copy/ paste the code back. While I recommend to read anything before you paste, I don’t think it is useful on this site to have this replacement. I am assuming this smart replacement is…

[goedible]

I did it! THANKS FOR YOU HELP. I still have to rename the column on the species table: id and rename it to “species_id”. Any tips to do that?

[Villeroy]

<hsql_install_dir>/hsqldb/doc/guide/databaseobjects-chapt.html#dbc_renaming