Ask Your Question

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

asked 2018-08-31 01:00:29 +0200

Liberty Belle gravatar image

LO 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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-08-31 01:12:24 +0200

Ratslinger gravatar image

updated 2018-08-31 02:54:45 +0200


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

edit flag offensive delete link more


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

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

Ratslinger gravatar imageRatslinger ( 2018-08-31 01:15:38 +0200 )edit

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

Liberty Belle gravatar imageLiberty Belle ( 2018-08-31 04:27:26 +0200 )edit

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, but I'm a little lost.

Liberty Belle gravatar imageLiberty Belle ( 2018-08-31 04:30:07 +0200 )edit

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'
Ratslinger gravatar imageRatslinger ( 2018-08-31 04:41:56 +0200 )edit

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;'

Liberty Belle gravatar imageLiberty Belle ( 2018-08-31 04:53:18 +0200 )edit

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?

Ratslinger gravatar imageRatslinger ( 2018-08-31 05:04:03 +0200 )edit

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.

Liberty Belle gravatar imageLiberty Belle ( 2018-08-31 05:13:32 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2018-08-31 05:35:19 +0200 )edit

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.

Liberty Belle gravatar imageLiberty Belle ( 2018-08-31 05:58:42 +0200 )edit

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;'

Liberty Belle gravatar imageLiberty Belle ( 2018-08-31 05:59:31 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-08-31 01:00:29 +0200

Seen: 726 times

Last updated: Aug 31 '18