Base | Firebird primary key/autovalue/BigInt field collides with SQL

lonk · January 22, 2020, 7:28am

From the error message of Calc BASIC:

It looks like SQL tried to insert new record with ID=1 but Firebird didn’t allow for ID=1 exists.

Did I misunderstand or how can I handle ?

Ratslinger · January 22, 2020, 6:08pm

Hello,

The problem is with the auto-increment starting point. As it is not known how you got the initial data into the table, it is probable it was imported in some way. This then did not set the value correctly. See this post → After updating I get error message when saving in Base.

You can correct the problem by running the following statement from Tools->SQL...:

alter table "AccountCodeLevel1" alter "ID" restart with 5;

lonk · January 23, 2020, 3:29am

Dear @Ratslinger,

Yes, I created the table by copying from Calc and pasting in Base/Firebird.

Khob Khun Krab.

lonk · January 23, 2020, 4:46am

Dear @Ratslinger,

Does this kind of problem happen with HSQLDB database or others ?

Ratslinger · January 23, 2020, 5:00am

Just Firebird embedded. See tdf#119962

lonk · January 23, 2020, 1:17pm

Dear @Ratslinger,

Thank you so much again.