DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Unique key violation in 10g

  1. #1
    Join Date
    Jul 2006
    Posts
    1

    Angry Unique key violation in 10g

    Hi,

    I have a insert statement with values taken using a series of selects from 10 different tables.

    Insert into TABLE 1 (col1, col2 col3, col4, col5, col6, col7)
    Values (
    select * from view 1
    UNION
    select * from view 2
    UNION
    select * from view 3
    UNION
    select * from view 4
    UNION
    select * from view 5
    UNION
    select * from view 6
    UNION
    select * from view 7
    UNION
    select * from view 8
    UNION
    select * from view 9
    UNION
    select * from view 10
    )

    This TABLE 1 has unique key (composite) contraint on col1, col2, col3, col4 & col5. All views from 1 to 10 have same columns including data types.

    In my understanding UNION should eliminate duplicate rows. However this is giving me unique key violation on TABLE 1.

    I verified data to find any duplicates for col1 to col5 (using count * and group by), but the result has no duplicates.

    This is working fine in Oracle 9i, but failing in Oracle 10g.

    Any thoughts?

    Vinay

  2. #2
    Join Date
    May 2005
    Location
    France
    Posts
    34
    You are right, UNION ALL keeps duplicates but UNION does not.

    So the UNION has no duplicates taking into account the 7 columns, but your unique key is only on the 5 first columns, it happens that you must have duplicates of the 5 first columns but columns 6 & 7 different for these "duplicates".

    for instance :

    1 2 3 4 5 10 100
    1 2 3 4 5 10 101

    HTH & regards,

    rbaraer

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