Finding primary key violations

[ca9mbu]

Some time ago every record in one of my tables got duplicated (the primary keys had been disabled for some reason - so these duplicates were allowed). I did a select distinct * in order to remove the duplicates, but now the primary key (a compound key on three fields) will still not enable due to it being violated. How can I find out which record(s) would violate the primary key I'm trying to enable. I tried the following but to no avail:

select STAT_TIMESTAMP, STAT_DTO_ID, STAT_ID
from STATS
minus
select distinct STAT_TIMESTAMP, STAT_DTO_ID, STAT_ID
from STATS;

This returns no rows, suggesting to me either the code isn't doing what I expect it to do (i.e. return none distinct primary keys values) or that there is nothing that would violate the primary key!

Thanks for any light you can shed on this problem,

Matt

[nealh]

No, the MINUS will remove any duplicates in the same way as a UNION. There are several ways of detecting duplicates:

1) Group by..having..

select STAT_TIMESTAMP, STAT_DTO_ID, STAT_ID, count(*)
from STATS
group by STAT_TIMESTAMP, STAT_DTO_ID, STAT_ID
having count(*) > 1

2) Rowid:

select * from
stats s1
where exists
(select 'x' from stats s2
where s1.statdto_id = s2.stat_dto_id
and s1.stat_timestamp= s2.stat_timestamp
and s1.stat_id = s2.stat_id
and s1.rowid <> s2.rowid)

3) Use an EXCEPTIONS ...INTO clause when enabling the constraint.

[ca9mbu]

Many thanks for such a swift and accurate response. The first solution worked fantastically, thankyou.

Matt