At 11:11 PM 31/05/2007, you wrote:

>Hi all,
>
>We have a very strange situation with a FireBird database: a table with
>a PrimaryKey has duplicate values.
>
>The table is defined as:
>
>SQL> CONNECT D:\FIREBIRD\CORRUPT.FDB USER SYSDBA PASSWORD masterkey;
>
>Database: D:\FIREBIRD\CORRUPT.FDB, User: SYSDBA
>
>SQL> SHOW TABLE CCP_ARBOLPRECIOS;
>
>IDCATPRO NUMERIC(18, 0) Not Null
>
>CLAVE VARCHAR(100) Not Null
>
>FECHASALIDA DATE Not Null
>
>PRECIOS BLOB segment 80, subtype TEXT Nullable
>
>CONSTRAINT INTEG_9:
>
> Primary key (IDCATPRO, CLAVE, FECHASALIDA)
>
>SQL> SHOW INDICES;
>
>RDB$PRIMARY3 UNIQUE INDEX ON CCP_ARBOLPRECIOS(IDCATPRO, CLAVE,
>FECHASALIDA)
>
>RDB$PRIMARY2 UNIQUE INDEX ON CCP_ARBOLPRODUCTOS(IDCATPRO)

This looks quite weird. Did you create that index named RDBPRIMARY2
? Are you able to drop that index? (I suspect that someone has been
meddling with your system tables...Firebird won't allow you to have
two primary keys.)

>But id does have duplicates:

I think that unique index RDB$PRIMARY2 is no-op. It is certainly not
reported as a key.

As for the primary key, the most obvious guess is that the database
is ODS 9, in which case your DATE field is a timestamp, your
NUMERIC(18,0) field is stored as DOUBLE PRECISION, the database is
dialect 1 and isql will therefore be connecting to it as a dialect 1
client. Depending on where the data came from, there might be
thousands of records having identical date-parts but distinct
time-parts; and unreliable display of these pseudo-integers at these
extremities of scale.

For a reality check, find out the ODS version of your database and
report here. For this, use gstat -h.

The other is the VARCHAR(100) that is used in the key. There is
plenty of room for error there, involving non-printable characters,
mismatched character sets, with/without case-insensitive collations
and so on. You haven't provided any information about the default
character set of the database or what character set you are using for
the client (SET NAMES ...)

./heLen