How may I set up the auto-value countering for a primary key in Base?

[mrmister]

Hello
I have a LibreOffice Base table, and it has a Primary Key, which is an INTEGER and it has 37 registries.
The primary key was set-up as autovalue, because what I want is to move forward adding new registries and the value add +1, that is if the last value was 37, the next record would be 38, 39, 40 and so on…

The problem is (and I don’t know why is this happening) that when I add a new record… the next record don’t put the correct autovalue, so the if last record was 37, when I add a new record the autovalue says the next record to 37 is 40 !!! and that is incorrect, it doesn’t follow the correct number ordering, it should 38 and not 40… the autovalue is crazy…

The last record in the primary key was 37
The next one should be 38 and not 40 (as the autovalue suggest that is the correct number, 40, for the next record)
How may I correct the autovalue to make it follow correctly the next number and put 38?

[PChitnis]

Please allow me to state your question as I understand it! Your table has an auto-incrementing field. You have 37 (or say x) records in the table. The next record should be 37 (or x+1). But you are getting 40 (that is greater than x=1).
Did you delete/erase some records? The auto-increment goes on adding to the last recorded value! So if you added serial no. 38 and 39 records and the erased these, the next record will be 40 not 38! Check “reset auto-increment” of your database. If you must have a serially numbered primary key do not use auto-increment.

[PChitnis]

Its an alter table command. Something like {ALTER TABLE “table_name” ALTER COLUMN "column_name " RESTART WITH xx;}. Check Base Tutorial by Mariana. Keep in mind that restart number must be greater than highest existing entry in the primary index. If you must have the numbers in a continuous series do not use auto increment or have another (primary) key field which is manually filled. Check documentation in detail.

[mrmister]

How do I check “reset auto-increment”? where is that option?

[iplaw67]

It is not an option that you will find in the GUI, you have to enter a command via the Tools > SQL menu. Read the documentation for hsqldb 1.8 available here