All Forums Database
Kishore_1 208 posts Joined 03/10
13 Sep 2011
Unique secondary index violation error

Hi,

There is a table with the following definition:

Create table Table1

(

col1 SMALLINT,

col2 INTEGER,

col3 CHAR(5),

col4 INTEGER,

...,

...,

col8 VARCHAR(20),

PRIMARY KEY (col1,col2,col3)

)

PRIMARY INDEX  nupi_tab1(col1,col2,col3)

INDEX (col2);

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?

WAQ 158 posts Joined 02/10
14 Sep 2011

Thats because of the "PRIMARY KEY" defined on the table. Indexes are created when PKs are definded on the table.

Kishore_1 208 posts Joined 03/10
14 Sep 2011

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?

VandeBergB 182 posts Joined 09/06
14 Sep 2011

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.

Some drink from the fountain of knowledge, others just gargle.

CarlosAL 512 posts Joined 04/08
14 Sep 2011

Kishore_1:

>>"There is a table with the following definition:"

No, there isn't, and there cannot be.

The DDL provided is incorrect. You cannot declare PK's on nullable columns like yours. You cannot have two indexes with the same columns (the PK index and the NUPI). The table defined as described by you cannot simply exist.

Please give REAL PROPER CODE if you want us to help you.

Cheers.

Carlos.

 

Jigar 70 posts Joined 09/11
15 Sep 2011

You have dups on col1,col2,col3 .

Raja_KT 1246 posts Joined 07/09
29 May 2014

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
 ,STG.address_end_dt
 ,STG.source_system_code)

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ToddAWalter 316 posts Joined 10/11
30 May 2014

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.

You must sign in to leave a comment.