Unique secondary index violation error

Database
Enthusiast

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?

7 REPLIES
WAQ
Enthusiast

Re: Unique secondary index violation error

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

Enthusiast

Re: Unique secondary index violation 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?

Enthusiast

Re: Unique secondary index violation error

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.

Junior Supporter

Re: Unique secondary index violation error

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.

Enthusiast

Re: Unique secondary index violation error

You have dups on col1,col2,col3 .

Enthusiast

Re: Unique secondary index violation error

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)

Teradata Employee

Re: Unique secondary index violation error

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.