Unique Constraint Errors After Upgrade to Firebird 5

[Biju George]

Hello everyone,

We’re encountering a persistent issue with unique constraint violations, and would appreciate some help or suggestions from the community.

We were previously using Firebird 2.6.4, and occasionally received
violation of PRIMARY or UNIQUE KEY constraint
errors even though there were no actual duplicate records in the table.

At that time, performing a backup and restore (gbak) always resolved the issue.

Recently, we upgraded to Firebird 5, and now we are facing the same type of unique constraint errors, but this time:

• Backup and restore no longer fixes the problem.

• Dropping and recreating the constraint also does not help.

• The issue occurs only with a few specific records.

• We have verified that there are no real duplicate key values.

• Index and constraint definitions look correct.

• Backup/restore completes without errors.

• Forced Writes is ON.

Has anyone experienced similar behavior in Firebird 5?

Could this be related to collation or character set differences, or is there a known issue with unique index handling after an upgrade?

What additional steps would you recommend to diagnose or correct this?

Any guidance would be greatly appreciated.

Best regards,
Biju George
www.lenvica.com

[Dimitry Sibiryakov]

Biju George wrote 05.11.2025 7:58:
> What additional steps would you recommend to diagnose or correct this?

Start from publishing here the definition of fields and constraints that
"looks correct" for you.
Then find out a full history of record versions for these problematic
records, not only final state.
There is no delayed constraints so if a record is included into a mass
update, intermediate states can produce constraint violations. For example
`UPDATE TABLE T SET F=F+1` may throw unique constraint violation or not
depending on execution path.

--
WBR, SD.

[Biju George]

Thank you for the response!

Definition of fields

CREATE TABLE ATT_REG
(
  REG_ID INTEGER NOT NULL,
  REG_EMP_ID INTEGER NOT NULL,
  REG_TYP_ID INTEGER NOT NULL,
  REG_BGN TIMESTAMP NOT NULL,
  REG_END TIMESTAMP NOT NULL,
  REG_VALUE DECIMAL(9,5),
  REG_APPROVE SMALLINT DEFAULT 0 NOT NULL,
  REG_REGDTTM TIMESTAMP NOT NULL,
  REG_USER_ID INTEGER NOT NULL,
  REG_DESC VARCHAR(100),
  REG_PROCESSED SMALLINT DEFAULT 0 NOT NULL,
  REG_HLD_ID INTEGER,
  REG_STATUS SMALLINT,
  REG_CREATOR SMALLINT,
  REG_DURATION DOUBLE PRECISION,
  REG_DAY_DURATION DOUBLE PRECISION,
  REG_DATE DATE,
  REG_JBC_ID INTEGER,
  CONSTRAINT PK_ATT_REG_REG_ID PRIMARY KEY (REG_ID),
  CONSTRAINT UQ_ATT_REG_0 UNIQUE (REG_EMP_ID,REG_TYP_ID,REG_BGN,REG_END)
);

[Dimitry Sibiryakov]

Biju George wrote 06.11.2025 5:22:
> Definition of fields

Which constraint throws violation and on which operation?

--
WBR, SD.

[Pieter Bas Hofstede]

Could the timezone-support added by FB4 bring some issues converting non-TZ-timestamps to (by default) DATETIME with TZ in the FB5-database?

I can imagine some timestamps in the past with daylight saving could get a new timestamp when taking daylight saving into account (just a guess).

Try set  DataTypeCompatibility = 3.0 in firebird.conf and restore again.

You have to take multiple steps into account when migrating from <= FB3 to FB5.

More info: https://ib-aid.com/download/docs/fb5migrationguide.html

Op donderdag 6 november 2025 om 09:57:51 UTC+1 schreef sd:

[Mark Rotteveel]

On 06/11/2025 10:06, Pieter Bas Hofstede wrote:
> Could the timezone-support added by FB4 bring some issues converting
> non-TZ-timestamps to (by default) DATETIME with TZ in the FB5-database?

That doesn't happen "by default", except maybe when you didn't replace
CURRENT_TIMESTAMP with LOCALTIMESTAMP.

Besides, those same problems would exist previously with daylight
savings changes. And if anything, it would be less likely to happen if
Firebird actually did what you think (converting non-TZ timestamps to TZ
timestamps).

> I can imagine some timestamps in the past with daylight saving could get
> a new timestamp when taking daylight saving into account (just a guess).

> Try set *DataTypeCompatibility* = 3.0 in *firebird*.conf and restore again.

That setting only has effect on communication between client and server,
not on what happens within the server.

Mark
--
Mark Rotteveel

[Biju George]

We’ve now resolved the issue.

The root cause turned out to be related to our data access component, which wasn’t handling transactions correctly.
After we modified our code to use explicit transactions, the unique constraint errors disappeared.

Thank you all for your time and suggestions. Appreciate the support from the Firebird community and developers.