Finding primary key violations
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Finding primary key violations

  1. #1
    Join Date
    Sep 2000
    Posts
    26
    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

  2. #2
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    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.

  3. #3
    Join Date
    Sep 2000
    Posts
    26
    Many thanks for such a swift and accurate response. The first solution worked fantastically, thankyou.

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width