Helen,

> At 07:42 PM 19/07/2007, Lele Gaifax wrote:
>> ...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.
>> Argh! We detected other two dbs with the same problem, running under
>> different condition. Of the three, two are under WinXP (one multifile,
>> one not) while the other is under GNU/Linux. This latter in particular
>> has been running great since several years ago, but the check revealed
>> hundreds of dups in one of the tables. Amazingly, in this case the index
>> was found disactivated (a condition that obviously allows dups given the
>> nature of the code that insert them, ie "INSERT ... WHEN SQLCODE -803
>> UPDATE ..."), but there's no trace of who/when/why disactivated it. It
>> most probably happened since last backup/restore cycle, as the previous
>> copy of it does not present the problem and the index is active...
>
> During restore, activating the constraint indexes is one of the last
> things to happen. If the restore fails at that point, the restore
> will abort. You will have your database but it will be in an
> uncertain state with regard to any of your indexes. Make sure you
> create a verbose log of restores and **check it** before deleting the
> old database and kicking in the new one. (Please don't say you are
> using gbak -R for restores...)
>
>> In the other cases the unique contraint index is active, but it
>> nevertheless allowed the insertion of dups.
>
> If you are talking about an index on a character column then No. If
> the index for the UNIQUE constraint is active, then somehow you are
> letting in data that contains subtle differences. One space
> character will do it. On CHAR fields, one *trailing* space will do it.

When someone mentions duplicate values in e.g. a primary key, this
always reminds me to the following issue:
http://tracker.firebirdsql.org/browse/CORE-104



--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com