Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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

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.