Frequently asked questions - Base

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

• Wiki Home
• Development
• Design
• QA
• Events
• Documentation
• Website
• Localization
• Accessibility
• Marketing
• Diversity
• Wiki Help

• Documentation Home
• Official Publications
• Other Resources
• FAQ
• HowTo
• Macros
• Docs Development

• FAQ Home
• General
• Writer
• Calc
• Impress
• Draw
• Math
• Base

How can I re-start continuous numbering for a primary key?

• Context: in the course of additions and deletions of rows in a table numeric primary key values will become non-sequential. Although this has little or no impact on the database operation itself, you may wish to restore continuous numbering.
• This FAQ does not consider the instance where one or more relations have been defined using this key to link this table to other tables, when this data will also exist in those tables. Of course, in that instance, you will have to change the data in the other tables too or the relations between the tables will be broken.
• Apart from this unconsidered case, various situations present themselves:

The primary key is autogenerated and the table do not include data

• Execute the following SQL command via the "Utils -> SQL" menu

ALTER TABLE 'tablename' ALTER COLUMN 'primary key name'  RESTART WITH 1

The primary key is autogenerated and the table contains data

Starting situation: non-continuous numbering

• Create the following numbering: the table contains 20 rows, for example, removing the last row, restart numbering at 15.

Use the SQL command above, modifying the starting value to 15.

• Completely renumber (changing the existing keys): use the "copy table" wizard.

Copy and paste the table or move it slightly with the mouse: the wizard will appear.

1. Supply a new name for the table
2. Choose the option Structure and Data, click on Next
3. Select all the fields except the primary key
4. Click on Create. LibreOffice will suggest creating a primary key: choose No
5. The new table is created. Modify it by adding a primary key field, autogenerate the value, save.

• The above process in images:

Recreating the primary key

Table copying wizard 1	Table copying wizard 2
Table copying wizard 3

The primary key is not defined as autogenerated and the data doesn't contain any data

• No modification is required: the field is editable and can have any value desired.

The primary key is not defined as autogenerated and the table contains data

• Since the primary key is not automatically generated the system does not generate values, sequential or otherwise. However, we can create a counter for this in an SQL command executed via the menu Utils > SQL

Use of a counter

Starting point: non-sequential non-automatic numbering

Execute via the Utils > SQL menu

Result

• SQL command:

CREATE SEQUENCE NUMENREG AS INTEGER
UPDATE "T_A" SET ID = NEXT VALUE FOR "NUMENREG"
DROP SEQUENCE "NUMENREG"