--- In firebird-support@yahoogroups.com, "ainpoissee" <ainpoissee@...>
wrote:

>
>
> OK, I now pumped data from that table to an table without any

constraints:

>
> CREATE TABLE TAB_GRAAFIK_TEST
> (
> UID INTEGER,
> VAHETUS INTEGER,
> TOOLEPING INTEGER,
> MUSTAND INTEGER,
> KUUPAEV DOM_KUUPAEV,
> CHANGED DOM_TIMESTAMP
> );
>
> and then tried
>
> select count(uid), uid from tab_graafik_test group by uid order by 1

desc

>
> and it appears that there is indeed 2 uid which aren't unique!
>
> UID VAHETUS TOOLEPING MUSTAND KUUPAEV CHANGED
> ==== ======= ========= ======= ======= ========
> 139972 1 24 08.08.2007 26.06.2007
> 11:12:00
> 139972 1 24 08.08.2007 26.06.2007
> 11:16:45
> 139973 1 24 09.08.2007 26.06.2007
> 11:12:04
> 139973 1 24 09.08.2007 26.06.2007
> 11:16:49
>
> Note that only difference is in "Changed" field.
> However, when running this query against original table (TAB_Graafik),
> there is no dublicates! So it seems that data from an old transaction
> is somehow has survived, but it is visible in only in some special
> condition?

The primary key uses an index to determine whether a given value is
unique. It would appear that at some stage, this index has been
corrupted, and when the second one has been inserted, it did not
realise that one already existed!

The problem with the query:

select count(uid), uid from tab_graafik_test
group by uid
order by 1 desc

Is that Firebird is going to use the index we already know is corrupt,
so it won't see one of those records. You could try something like.

select count(uid+0), uid+0
from tab_graafik_test
group by 2
having (count(uid+0)) > 1
order by 1 desc

Once you locate the duplicate UID, you will have to manually remove
one of them, then everything. I suggest dropping and redeclaring the
primary key constraint to make sure the index is rebuilt.

Adam