Unique key violation in 10g

[vinayaca]

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

[RBARAER]

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