Change existing DB primary key to autovalue

English
,
#1

Trying to change the primary key to autovalue in an existing database. I see the suggested solution here, but when I try to follow it I get asked “The column ‘ID’ could not be changed. Should the column instead be deleted and the new format appended?” and then, after saying yes, the following error:

ID autoincrement
firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE Table1 failed
*SQL error code = -607
*Invalid command
*Specified domain or source column SQL_LONG does not exist
caused by
‘ALTER TABLE “Table1” ADD “ID” SQL_LONG NOT NULL’

Alternately, trying to just delete the field and add a new one results in this error:

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 48
*GENERATED
caused by
‘isc_dsql_prepare’

If there’s no way to change the field, is there a way to extract all the data from the other fields and import it into a new database, or some other option?

I’m running LibreOffice 7.0.0.3 on Windows 10.

Importing data from existing DB table into new
#2

In theory, the Base interface should just let you change an ID_PK (primary key) column to autovalue (auto-increment) from the TABLE DESIGN window - as you referred to HERE.

But this does NOT seem to work with current versions of LO, at least using FIREBIRD EMBEDDED databases. Maybe it’s a BUG - see COMMENT 3 and 4 of that bug report. So it looks like you are stuck with re-creating the autovalue PK in a brand-new table and importing your data. (Anyone got a better idea?) POST again (in a new QUESTION) if you need any further help with that part.

UPDATE 4 Oct 2020

It looks like you are not the only one who has experienced this problem - see HERE

Also see this BUG report - which seems a bit pessimistic re the future of FB in LO Base.
https://bugs.documentfoundation.org/show_bug.cgi?id=130345

I have found some directions on how to change the PRIMARY KEY in a FB table. It looks complicated.
http://www.firebirdfaq.org/faq380/

#3

Thank you for taking the time to answer. It sounds like importing the data to a new file would be the easiest way to deal with it so I will make a separate question about that as suggested.

#4

I think this is answered here? Near the bottom. Trying to figure it out

Raslinger says:
Now as for setting auto increment after the table is created see tdf#112491

If records have not been yet added, you can change by using SQL. First drop the field:

alter table "YOUR_TABLE" DROP "YOUR_FIELD"

Then add the new field:

alter table "YOUR_TABLE" ADD "YOUR_FIELD" INTEGER generated by default as identity primary key;

After doing this, you need to save the Base file and close/re-open to see the change.

For the “Entry required”, again use SQL:

ALTER TABLE "YOUR_TABLE" ALTER COLUMN "YOUR_FIELD" SET NOT NULL

If there is data in the table when doing this and NULL fields in the column you are setting, you will get an error:

1: firebird_sdbc error:
*unsuccessful metadata update
*Cannot make field YOUR_FIELD of table YOUR_ABLE NOT NULL because there are NULLs present
caused by
'ALTER TABLE "YOUR_TABLE" ALTER COLUMN "YOUR_FIELD" SET NOT NULL '

Add data to all NULL fields for that column and re-run the SQL.

Also after doing this, you need to save the Base file and close/re-open to see the change.

#5

This is what I have done.

created blank database (ie copied only the headings)

tools > SQL

alter table “YOUR_TABLE” DROP “YOUR_FIELD”

check to see if it has taken. (ie open and close the table)

alter table “YOUR_TABLE” ADD “YOUR_FIELD” INTEGER generated by default as identity primary key;

check to see if it has taken (ie open and close the table)

ALTER TABLE “YOUR_TABLE” ALTER COLUMN “YOUR_FIELD” SET NOT NULL

check to see if it has taken (ie open and close the table)

ALTER TABLE “YourTable” ALTER “Your_Field” POSITION 1

all info gleaned from these posts

#6

eeeer did not work, but this (your follow up question) was useful. Importing data from existing DB table into new - #4 by Ratslinger

#7

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 asks you to add a primary key but adds a non-incrementing integer.
Additional tip: Append your auto-ID as last column. This makes it easier to copy data between tables or from spreadsheets.

1 Like
#8

Been going around in circles with this. I’ve been trying the ALTER TABLE TBL ALTER COLUMN ID INT IDENTITY but have not managed to get it to work in my firebird database. Will try and play some more later.

Thank you.

#9

With HSQL, the statement would do the trick. With Firebird it seems to be impossible to fix a table with missing auto-ID, which is one of the reasons why I do not use FB with Base.
If you are familiar with Firebird, you are able to try this or that work-around. But wth Base as learning environment, it is impossible to use that database in productive ways.

I never copy table data from Calc or elsewhere to a new table. I always create an empty database table with all its column types, indices, relations and constraints. Then I paste data into the prepared structure.

#10

page 137 of the user guide is categorical, it is impossible.
.
Changing Identity Columns:
For identity columns (GENERATED BY DEFAULT AS IDENTITY), it is possible to restart the sequence used
for generating identity values. If only the RESTART clause is specified, then the sequence resets to the
initial value specified during CREATE TABLE. If the optional WITH start_value clause is specified, the
sequence will restart with the specified value.
.
It is not possible to convert an existing column to an identity column, or to convert an identity
column to a normal column. Firebird 4 will introduce the ability to convert an identity column to a
normal column.
.
Restarting is currently subject to a bug: the first value generated after a restart is 1
(one) higher than the configured initial value (or the value specified through WITH
.
so if we execute this sql then add a record to “MyTable” then the value of “ID” in that record will be 2.

alter table "MyTable" alter "ID" restart with 1;
#15

It has been commented before by a very reputable LO member, whose posts are very helpful and whose user name starts with “Rat”… “there is no logical reason for a table to have a certain row order…” (my paraphrase). I guess at least two of us would disagree.

#11

First verify that the primary key field of the table is not part of a relationship with another table. Close all open dB objects, then go to Tools>Relationships. In the relation design window, if a relationship exists between the primary key of the table in question and another table, taking note of the existing relationship, then delete it by left clicking the connecting line and select “delete” from the right-click menu. “Save” the relationship design, then close the window. “Cntrl-S” to save the dB. Open the table and be sure there is a unique “Integer” in the primary key field of each existing record. This is very important as primary key fields must contain unique, and not null values, and Autoincrement must be integers. Save the table again and close. Save the dB again! Now Edit the table to change the primary key to autoincrement = “Yes”. Save the table and close. Again Cntrl-S or “Save” the dB. Reopen tools>relationships and reconnect the previous relationship as before by left clicking on the primary key field and holding until moving the cursor over the related field in the child table, then release. A connecting line should appear meaning an innerjoin (default) relationship has been successfully established! Save and Close the relation design window. Save the dB again! I usually close and restart the dB to ensure Libre has properly reconfigured the “schema”. Should be good to go.

#12

Error code: 1

firebird_sdbc error:
*unsuccessful metadata update
*Cannot make field ID2 of table Persons NOT NULL because there are NULLs present
caused by
‘ALTER TABLE “Persons” ADD “ID2” INTEGER NOT NULL’
at /home/buildslave/source/libo-core/connectivity/source/drivers/firebird/Util.cxx:69

which is plain WRONG. The “Persons” table was a simple “Name” and “ID” list with the integer ID being the primary key. “Persons” is the one and only table in the database. Now the ID-column ist lost which would make it impossible to restore any relation that may have been existed.

#13

If tblPersons is the one-and-only table, there would be zero relationships to other tables. However, occasionally LO will form a relationship from the primary key field… to itself, or another field in the same table. Make sure this is not the case! Otherwise it seems that at least one of the values in the primary key is Not an integer (letter “O” instead of zero (0) in value) or their is a hidden space (’ ‘) somewhere in the primary key. To correct these possible issues, either manually check each primary key entry, or use the SQL command tool, depending on how many rows the table contains. (Tools>SQL) In the SQL Command box, type: UPDATE “Persons” SET “ID” = REPLACE(“ID”,’ ‘,’’). Notice the single (’) quote marks used for designating the space character …’ ‘… and its replacement …’’… Execute the sql. This eliminates a single hidden space in the ID field. You may have to repeat this a few times if there are several hidden spaces. A successful indication should appear in the Status box. Now change the replace portion to: REPLACE(“ID”,‘O’,0). Execute the sql again. This replaces any text Os in ID to the number 0. Try the autoincrement change again.

#14

Like 90% of users I just copy some data from Calc, insert structure and data, “add primary key” =yes and fail. Every time, reproducible, since 2007 (OOo 2.0).

#16

I am able to “recreate” the populated table with the Primary Key (“ID2”) being Auto-increment without losing data (save the data anyway, just in case)… by SQL command or editing the script file with something like:
– CREATE CACHED TABLE “My_Table”(“ID2” INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,“Other” VARCHAR(64)) –

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.