I have a table where the primary key changed its numbering scheme from 1, incrementing by one to a much higher number, 1004 and incrementing also by one. I know that the numbering system doesn’t affect the way the table works, but I still would like it to start at 1. I found a SQL statement that is supposed to work, but doesn’t - I get the error message: This operation is not supported in statement [alter table “OHA_DB-WO-Old” alter column “W/O_Key” restart with 1]. The SQL statement I entered is included in the error message “alter table “OHA_DB-WO-Old” alter column “W/O_Key” restart with 1”. Does anyone know what the problem is or a solution that works. Thanks, Erik.
If there appears a message “not supported”: Which database do you use? Internal HSQLDB, internal Firebird or an external database?
And: Isn’t there any row in the table? You want to start with ‘1’, so the maximum of “W/O_Key” has to be ‘0’ (HSQLDB) or ‘1’ (Firebird).
If the database is of type embedded hsqldb (listed at the bottom of the LO window with your odb open) then, after saving a copy of the odb,
- First, if you wish to maintain the referential integrity of current data, then in the relationships tool of the main LO window menu (Tools / Relationships), make sure that each relationship between the Primary Key table and all of its related foreign key tables has its update property set to cascade update. This will update the foreign key of related tables to match the Primary Key, which you are about to change. Save the relationships. (ctrl+s)
- Second, edit the primary key table and manually renumber the primary key field (“ID”?) in the order you desire, starting from 0. If you enter a value that is a duplicate or invalid, there will be an error, simply don’t save the record and try another valid integer or value. After completing, close the table and save if prompted. Also save odb.
- Third, back in the relationships tool, delete the Primary Key relationships between the primary key table and all other foreign key tables. Save relationships, then also save (ctrl+s) the odb.
- Fourth, edit the table of the primary key, right click on the far left row marker of the primary key (“ID”?) and untick “Primary Key”. Save the table! Now set the PK’s AutoValue field property to No. Select any other field marker in the table then save and close the table, and save the odb! If an error occurs here, then generally something was missed above. Simply don’t save the table changes and review the previous procedures.
- Fifth, edit the PK table again, this time remark the PK (“ID”?) as Primary Key. Save the table! Select the PK marker again and set the AutoValue to Yes! Select any other field marker in the table before saving and closing the table, and then save the odb!
- Finally, back in the relationships tool, recreate the relationships between the Primary Key table and each of the foreign key tables as before, while also enabling cascade update options. Save and close relationships, then save the odb! Confirm the reset Autovalue by adding a new record into the table.
Each of the many save operations in the procedure are critical, so do not forget or ignore them! There is a much easier method to do this but involves decompressing the odb archive and editing the script file. (somewhat risky to data)
Renumbering an autovalue Primary Key is usually an exercise in futility. Except for debugging purposes you would normally hide that field when you design the forms, In use it rarely remains sequential for very long. A miss-typed entry or deleted one leaves a hole in the numbering. If you have Relationships already defined and Data entered in those related tables then I strongly suggest that you abandon this misguided quest.
Anyway another approach for an Embedded HSQL Database
- Make sure you have a backup .odb just in case!
- Select the table you want to renumber and press Ctrl-C to copy the table to the clipboard
- Press Ctrl-V to open the Copy Table Dialog.
- Change the Table Name (I often just append 01 to it)
- Change the option to Definition and Select Create on the buttons below
- Open the new table and enter something for record zero, it doesn’t matter what since you will delete it in step 13, then Save the edited table
- Select the table you want to renumber again and press Ctrl-C
- Select the new table and Press Ctrl-V to open the Copy Table Dialog.
- It should display the name of the new table and for Option select Append Data
- From the buttons below select Next to open the Assign Columns dialog
- In the Source Table column, make sure the Primary Key field does NOT have a check mark in front
- Select the Create button.
- Open the new table to verify it has the values you want, then Delete record 0
- Now Rename the original table then Rename the new table.
And yet the suggested solution by UnklDonald does not preserve existing relationships if the Primary key need be reordered as requested. If followed properly, the skyandrews solution still works reliably, even after 5 years of use. If a big mistake is made, that is where the backup copy comes in.
I use the sequential order and absence of gaps in the Primary key to help verify the data integrity of the whole table. For me, having a properly ordered and gapless primary key increment is quite important! It probably stems from my early db days in MS Access, where a menu button exists that when pressed, will “Compact and Repair Database”. Basically it resets and reorders Autoincrement table fields in the db while maintaining foreign key relationships, much the same as the skyandrews suggestion.