UNIQUE PRIMARY INDEX (UPI) is unique and cannot have any duplicate column values. If you insert a row with same primary index value that is already in the table, the row will be rejected as a UPI enforces UNIQUENESS for a column. A UNIQUE PRIMARY INDEX (UPI) will always spread the rows of the table evenly amongst the AMP’S UPI access is always a one – AMP operation.


        We have selected EMP-NO to be our PRIMARY INDEX because we have declared EMP-NO to be a UNIQUE PRIMARY INDEX there can be no duplicate employee numbers in the table.

UPI is ONE AMP Operation . It is suggestible to UPI set on correct column in the Teradata table. 

Example for UPI :

EMP-NO
DEP-NO
FIRST NAME
LAST NAME
SALARY
1111
1000
Smith
Jones
50000
3333
3000
Johnson
Stewart
45000
4444
4000
Peter
Jones
85000
6666
6000
Sandy
Vatish
96000
8888
8000
Matt
Stewart
52000
9999
9000
Leonia
Lacy
63000
   


TERADATA ENFORCE UNIQUENESS ON AN UPI

Data Distribution or storing a record in Unique Primary Index(UPI):

{ Please refer Primary Index Page for Diagram}

 Step 1: After record reaches the PE , Row hash is generated for the given row , In this case Uniqueness value is 0
Step 2 : The Teradata searches the Amp value in the hash map which is present in BYNET
Step 3 : After searching for the right AMP , the row is moved to the respective Vdisk which is under the AMP provided in step 2

Data Retrieval Unique Primary Index (UPI) :

Step 1: When Row came to PE , the respective Hash value is calculated
Step 2 : Based on the Row hash value the repective AMP is taken
Step 3 : The Row is searched in the Amp using the Physical row id provided and given back to PE.

Syntax for UPI Creation :

CREATE TABLE TABLENAME 
( Col1 datatype ,
Col2 datatype )
Unique Primary Index ( Col1);
      

 Sample UPI Creation with example :                   


CREATE TABLE sample_1.
(COL-A INT, COL-B INT, COL-C INT)

UNIQUE PRIMARY INDEX (COL-B);