FIREBIRD: Can't add new record to database, sdbc error: violation of PRIMARY or UNIQUE KEY

I can’t add a record to my Firebird database in LO 6.2.5. I get an error message that says:

firebird_sdbc error:
*violation of PRIMARY or UNIQUE KEY constraint “INTEG_9” on table
“tablename”
*Problematic key value is (“ID” = 15)
caused by
‘isc_dsql_execute’

Any assistance would be much appreciated.

Run this from menu Tools → SQL:

SELECT MAX(“ID”) FROM “TABLENAME”

Observe NUMBER in output field.

Then enter new command:

ALTER TABLE “TABLENAME” ALTER “ID” RESTART WITH NUMBER

You might also want to read Re-setting the primary key in Firebird embedded databases after deleting records

Kelsa this worked, thank you. The numbering sequence used by the primary key ID field had reset to a lower value in the range of previous records, using a duplicate primary ID number. This command reset that value to a number beyond the range previously used.

For reference to others using this information, you need to replace NUMBER with the numerical value you want to use as the new starting point for the primary key auto increment. For me it looked like this:

ALTER TABLE “MYTABLENAME” ALTER “ID” RESTART WITH 1500

Thank you for tip. In Mac I had the same problem and now I resolved, but it needed to save Base and to close and restart LibreOffice to take effect.

You most likely entered a duplicate record. Record uniqueness is determined by the primary key field. A value entered in this field must not clash, meaning it must be unique.

If you are new to databases then I suggest the following reading material: Relational database

Read the section describing what a primary key is.

You might also want to read up more on FirebirdDB specifics here.