There is a table with the following definition:
Create table Table1
PRIMARY KEY (col1,col2,col3)
PRIMARY INDEX nupi_tab1(col1,col2,col3)
When I try inserting records into Table1, it throws the error: Unique secondary index violation, while there is no secondary index defined on the table.Could any one suggest what's the error?
The primary is a composite one ,i.e,(col1,col2,col3) combination should be unique,not a single column.
So, when col2 is defined as secondary index, does it treat this as unique?
The secondary index is a NUSI, you didn't specify it as unique. I'd check the data that your loading, it sounds as though your column combination on the PK (col1,col2,col3) is not unique in the source file.
You could have shared more info.. about ddl, Indexes ....
Di you check the SELECT alone without insert? Duplicates may be there.
Performance wise also it will be slow if huge number of rows :).
How about adding OVER ( ORDER BY STG.address_op_sys_id, ,STG.address_start_dt
There was a SQL posted that no longer seems to be here but... that SQL used RANK to generate the _op_sys_id column. RANK in Teradata is not guaranteed to generate unique numbers, if two records by the RANK columns are equivalent, then the RANKS for those two records will be the same. If you want unique numbers generated then you need to use ROW_NUMBER rather than RANK.