Difficulty with setting AutoValue in split HSQLDB table

English
#1

LibreOffice V. 7.2.6.2
Split HSQLDB data base
Linux Mint v. 20

The Problem: My application is a document metadata tracker. The master table contains
a file name field. Many of the file names entered in this field contain a colon character.
Unfortunately, my backup system does not play nice with files that contain a colon in their
name. So, I renamed all these files, replacing the colon with an underscore. No problem.

This left the issue of making he same changes in the file name field of the master table.
I exported the data to a Calc file, made the appropriate changes, then imported the file into
a new master table. Again, no problem.

The difficulty starts here. The master table has a primary key field called ¨ÏD¨ which needs to auto
increment (AutoValue=yes). This field property did not set properly when I imported the data from Calc.
I used Villeroy´s suggestion in the post

to change the AutoValue property of the field:

ALTER TABLE “MasterTable” ALTER COLUMN “ID” INT IDENTITY

This worked as expected. BUT–when I try to enter data into the associated form I get the following
errors when the data is posted to the table.

Error inserting the new record /home/buildslave/source/libo-core/connectivity/source/commontools/dbtools.cxx:751

and

integrity constraint violation: unique constraint or index violation; SYS_PK_10129 table:
“MasterTable” /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/Object.cxx:175

Any thoughts? It´s most frustrating as I have already spent more time on this than if I had just taken the
brute force approach of individually editing each of the entries in the table. I imagine there is an SQL
solution as well, but … SQL!

#2

The command menu:View>RefreshTables should be executed whenever you modified the database structure through the SQL window or through some 3rd party tool.

#3

Yes. I did this.

#4

Could it be that your identity field is linked to some other table? See Tools>Relations

#5

No. Tools>Relationships shows no linkages.

#6

When you open the table and scroll to the buttom, the ID shows “Auto Value” in the very last row ? You can enter data directly into the table whereas the form raises the unique constraint error? Then your form sets some default value or something. Simply delete any form control representing the auto-ID. The ID serves technical purposes only. In most cases it can be hidden from the user interface.

#7

This might be a clue. Looking at the bottom of the table I find that the first 3 items I entered in the form were recorded as having an ID AutoValue of 1, 2 and 0. These records had previously been deleted from the table. It was only when it attempted to enter record that attempted to create an AutoValue of 3, which is already in the table that the error occurs.

When I try to enter a new record directly into the last row of the table the error occurs.

I use the ID field to connect to other sub-forms in the form. Perhaps this is bad practice.

There is no form control that shows the ID field in the form.

#8

@dougcb68,
.
integrity constraint violation: unique constraint or index violation; SYS_PK_10129 table “MasterTable”
the error message is crystal clear you are attempting to duplicate the primary key value.
.
to reset the value of IDENTITY:

  1. using menu:Tools→SQL
  2. paste this under Command to execute:
    select max(ID) + 1 from "MasterTable"
  3. activate Show output of “select” statements.
  4. hit Execute and note the Output value.
  5. paste this under Command to execute:
    alter table "MasterTable" alter ID restart with ?
    replace the question mark with the Output value.
  6. Hit Execute
#9

YES! This worked perfectly. After the clue that Villeroy gave me to look at the end of the table I had also
reached the conclusion that it was a key violation problem, but I just didn’t know how to fix it. Many thanks to both Villeroy and cpb.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.