We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2014-10-25 03:37:59 +0200

mrmister gravatar image

updated 2014-10-25 03:40:34 +0200

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 12:05:22.251394

2 Answers

Sort by » oldest newest most voted

answered 2014-10-25 08:49:25 +0200

PChitnis gravatar image

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.

edit flag offensive delete link more


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.

PChitnis gravatar imagePChitnis ( 2014-10-25 15:26:48 +0200 )edit

answered 2014-10-25 14:21:54 +0200

mrmister gravatar image

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

edit flag offensive delete link more


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

Alex Thurgood gravatar imageAlex Thurgood ( 2014-10-26 09:27:11 +0200 )edit

Question Tools

1 follower


Asked: 2014-10-25 03:37:59 +0200

Seen: 6,707 times

Last updated: Oct 25 '14