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

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