I'm developing a Household -> kitchen -> pantry database and forms using LibreOffice Base. I have a table called "nutrition". I was messing around with relationships, just trying to get used to the software.
Originally I had the idea to link the nutrition table to items in the pantry table, using the product name and brand, so that it would be easy to read and change coding at a later date.
Somewhen I had primary key identifier using name and brand. Other tables used ID. When I found out strID's affect performance, I decided to revert to auto Int ID, generating each downstream record as linked.
I wanted to change the primary key to ID (auto generated) to avoid using strID's. The software asked if I wanted to delete the column and create another using auto and correct structure. I said yes. When I refreshed, the column was indeed gone, but the new one had not been created.
I saw when I was poking around in the background that a column with label * has been assigned as the primary key, but I cannot access this in the GUI.
Now every time I try to manually change the ID to auto, and to primary key and save this error pops up. This works in other tables.
How do I fix?
SQL Status: S0011
Error code: -25
Attempt to drop the primary key in statement [ALTER TABLE "nutrition" ADD PRIMARY KEY ("ID")]
Thinking how to solve this, as there are a lot of fields in the nutrition table that I don't want to lose nor redo if possible (A calorie counter will be linked in later). Can I do a table dump, then add the new column "ID" in a command line in a new table?