Subject Re: [firebird-support] Firebird restore can not restore index due to duplicate value
Author Helen Borrie
At 07:10 AM 19/02/2010, you wrote:
>Hi all,
>I have a problem with index when I restore database. I have this message:
>>Action cancelled by trigger(3) to preserve data integrity.
>>Cannot desactivate index use by a PRIMARY UNIQUE Constraint
>When I deactivate Index in restore : it works well.
>So I decide to make gfix and I get this
>Summary of validay errors :
>>Number of record level errors: 6
>>Number of index page errors : 9
>>When I decide to check the table with problem of primary unique key, I make
>SELECT t.table_id FROM TABLE t
>GROUP BY table_id
>HAVING count(*) > 1
>But I find nothing.

You have mixed aliased and non-aliased references here, which is not supported in this version (although it is surprising you did not see an exception).

>How I can see my duplicate value ? (I use Firebird 2.1.3)

SELECT table_id, count(table_id)
HAVING (count(table_id) > 1)

>Do you now any tools to find quickly where the error come ?

No, sorry. But I would first want to do these three queries and see whether they come out with the same number:

select distinct count(table_id) from table
select count(table_id) from table
select count(*) from table