Re-setting the primary key in Firebird embedded databases after deleting records

[LibertyBelle]

LO 6.0.6.2 on macOS High Sierra 10.13.6

When using the embedded HSQLDB in LO, I am able to re-set / restart the primary key if I delete some rows from a table and want new records to start where the remaining ones left off, but when I try this using the embedded Firebird database, I get an error message and can’t do it.

Should I report this as a bug? Feature request? I think most people would like the option to have the primary key continue where the last current record leaves off.

Example: I have a table with records 1-30 with the primary key AutoValue set to Yes. I delete records 16-30. When I add new records, LO starts the primary key sequence at 31.

If I’m using the embedded HSQLDB and I want Base to “re-use” ID’s 16-30, I can edit the table, set the primary key AutoValue to No, save the table, save the database, go back into edit mode, once again set the AutoValue for the primary key to Yes, saving everything. Then when I add new records, the next record will start with 16 again, leaving no gaps in the primary key sequence.

Unfortunately, this doesn’t work at all when using the Firebird embedded database. When I delete records and then set the primary key AutoValue to No and try to save the table, I get an error message: “Warning. The column “ID” could not be changed. Should the column instead be deleted and the new format appended?”

I definitely don’t want to delete all my existing primary key values, so I’m stuck.

[Ratslinger]

Hello,

Restarting an auto increment value is done using SQL. The method you used for HSQL is awkward at best. Here is the SQL for Firebird:

alter table TABLE_NAM alter FIELD_NAME restart with NNNN;

where NNNN is the last valid number.

Run this from the menu Tools->SQL main screen of the .odb

[Ratslinger]

Sorry, I believe the menu is different on Mac - maybe Preferences?

Also just checked, this SQL is the same for HSQLDB.

[LibertyBelle]

It’s Tools > SQL on Mac as well. (Much of the rest of the Tools menu is in Preferences on a Mac.)

[LibertyBelle]

Both my table name and my field name have spaces in between two words (Employee Table and Employee ID).

Do I need single or double quotes around either or both of these? I’ve tried it every way I can think of, and I’ve gotten so many error messages I’d hate to even post them all.

I’m looking at this page https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-structure-identifiers.html, but I’m a little lost.

[Ratslinger]

I’m a bit picky here. Just a personal annoyance. You surround the names with quote marks. " - one on each side. Double quote marks "" is needed when doing some SQL in macros. ' is an apostrophe. SO it would look like:

alter table "TABLE Name" alter "FIELD NAME" restart with NNNN;

By the way, data for comparison is surrounded by apostrophe (single quote):

Select * From "My Table" Where "Field" = 'Hello'

[LibertyBelle]

You’re not picky. I think I said it incorrectly / sloppily. I’m still doing something wrong, though. I used the quote marks around both table and field names and after hitting Execute, I get this:
1: firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE Employee Table failed
*Column Employee ID is not an identity column
caused by
‘alter table “Employee Table” alter “Employee ID” restart with 16;’

[Ratslinger]

You are attempting to set a fields’ increment number but the field is set to NO for auto increment. This is used on a field set to YES. Will look for SQL to modify field to set to auto increment. Give me a few minutes.

Do you still have the table before you changed it?

[LibertyBelle]

That is truly strange, because the AutoValue was set to yes. I imported data from a spreadsheet that didn’t have any primary key value. I created one when I created the new table, and it did add a value for each row/record. No idea what happened.

[Ratslinger]

OK. If you have original data it may be better to re-create the table. Have to research how to set again as it is lost in all my testing.

Just tested Calc to Base as you stated & auto increment doesn’t seem to be set when creating as you did. It does create the key but not the setting in my test. What did work was to define the table first (including auto field), then copy data from Calc and append data to table making sure fields match. Once done, The Alter SQL statement does work.

[LibertyBelle]

Yes, I just found that out myself. I decided to create a whole new table using Design View. Then I did get the alter statement to work.

[LibertyBelle]

BUT there’s something else funny. When I told it to restart with 16, it actually restarted with 17. No problem, I thought. I’ll delete these rows again and change the alter statement to 15. But when trying to re-run it, I get the following error message:

1: firebird_sdbc error:
*violation of PRIMARY or UNIQUE KEY constraint “INTEG_1” on table “Employee Table”
caused by
‘alter table “Employee Table” alter “Employee ID” restart with 15;’

[LibertyBelle]

Even if I then redo the statement and ask it to restart with 16 or 17 or 18, I just keep getting the same error message (with the last number changed to reflect whatever number I input). Even if I choose a number beyond my original records, like 35 (I only had up to 30 originally) still I get an error. It doesn’t seem right that you can only run this once on a table.

[Ratslinger]

First, if you check my answer (modified version), it states to set to last valid number. Probably not clear enough but it means the next generated number will be one higher.

Now for the rest, I cannot duplicate. I can run this alter SQL any number of times without an error on the same type of table created from Calc. Where I do get a similar error is with four records in table (last ID = 4) and I set last value to 3 (next will be 4), then it results in a similar error when entering a record.

[Ratslinger]

However, this error is only stating the auto increment is already used and if I try to add the record again it is OK and so is the increment value. So in my example if I set it to 1, adding a record would give the error three times (ID = 2, 3, 4) because they are already in use, then at 5 it works.

[Ratslinger]

Was finally able to reproduce your error by setting my counter to a much higher number. However, the statement actually worked and the auto increment was as expected. Additionally, the error continued, no matter what it was set to, but worked each time until I closed the .odb & opened it again. Then the error was gone.

[Ratslinger]

Generating the error seems erratic but it appears the statement works and the error goes away by closing the .odb & re-opening. Seems to be a bug. The ability to set a fields auto increment from No to Yes is also a bug. Have not found a method to set this using SQL.

[LibertyBelle]

Got it. And you’re right – despite the error message, once I added new data Base did indeed restart the numbering exactly where I had asked. I just tried it and it worked fine.

[LibertyBelle]

I think I will report all these bugs. Especially troubling is the inability to change AutoValue from no to yes. It’s such an easy mistake to make, to overlook this when setting up a table. And you don’t even have the option if you use a spreadsheet to create a table. I am accustomed to the embedded HSQLDB which allows you to edit this after creating and saving the table.

[LibertyBelle]

@Ratslinger can you take a look and see if this is the same bug https://bugs.documentfoundation.org/show_bug.cgi?id=112491. If so, I don’t want to duplicate it. The original report is a little hard to follow, but reading through to the last comments makes me think it’s the same (unable to change auto increment after saving a table).