Subject Re: Attempt to store duplicate value
Author Adam
> Do you have any hint or check I could perform? What can trick the server
> to store duplicated keys in presence of a UNIQUE constraint? Is it a
> synthom of some corruption in the indexes?

There does not appear to be anything wrong with the logic at first
glance. There may be a problem with multiple simultaneous imports
because the ABC combination that causes a constraint violation may be
in an uncommitted transaction (so the update may do nothing), but this
could not explain additional records.

If you have declared a unique constraint, it is 'not your fault' if
the database engine lets you put multiple records that violate that
constraint. It is either a bug, evidence of corruption in the database
file (garbage in, garbage out). Obviously a user could disable the
constraint, but I am pretty sure Firebird would prevent it from being
enabled with those duplicates.

I am not aware of any specific bug in 2.0 that would cause corruption
in indices. As long as you are careful to never use a file system
level copy utility (except in conjunction with nbackup whilst the
database file is in a stable state), and as long as you have forced
writes enabled, I don't see how it happenned.

To repair, identify and remove offending records, you could use
RDB$DB_KEY to assist you with this. Drop the constraint and redeclare
it. I would perform a database backup then restore cycle just to be
sure the other indices are OK.