Ask Your Question
0

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

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

Ratslinger gravatar image

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

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

edit flag offensive delete link more

Comments

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 https://www.firebirdsql.org/file/docu..., 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

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.

Liberty Belle gravatar imageLiberty Belle ( 2018-08-31 06:02:47 +0200 )edit

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 gravatar imageRatslinger ( 2018-08-31 06:13:19 +0200 )edit

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 gravatar imageRatslinger ( 2018-08-31 06:16:20 +0200 )edit

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 gravatar imageRatslinger ( 2018-08-31 06:31:19 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2018-08-31 18:28:51 +0200 )edit

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.

Liberty Belle gravatar imageLiberty Belle ( 2018-08-31 19:14:42 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 232 times

Last updated: Aug 31 '18