Difference between Teradata Primary Index and Primary Key

If you have worked on other RDBMS then you must be aware of Primary KEY concept. In Teradata ,Primary Key does not play as important role as Primary INDEX. In this post, we will see difference between Primary Key and Primary Index.

One must not get confused between Primary Key and Primary Index in Teradata. Primary KEY is more of a logical thing however Primary INDEX is more of physical thing. In Teradata, Primary INDEX is used for finding best access path for data retrieval and data insertion and Primary KEY is used for finding each rows uniquely just like in other RDBMS.

5 differences between PRIMARY KEY and PRIMARY INDEX

#PRIMARY KEYPRIMARY INDEX
1PRIMARY KEY cannot be NULLPRIMARY INDEX can be NULL
2PRIMARY KEY is not mandatory in TeradataPRIMARY INDEX is mandatory in Teradata with exception of NOPI tables
3PRIMARY KEY does not help in data distribution.PRIMARY INDEX helps in data distribution
4PRIMARY KEY should be unique.PRIMARY INDEX can be UNIQUE (Unique Primary Index) or NON UNIQUE (Primary Index).
5PRIMARY KEY is logical implementation.PRIMARY INDEX is physical implementation.
Primary Key vs Primary Index in Teradata

Now we will see few scenarios to see how these two are handled in Teradata

I have not defined PRIMARY INDEX or PRIMARY KEY on table what will happen now

In this case, Teradata will check if any column is defined as UNIQUE, then it will make that column as UNIQUE PRIMARY INDEX else first column will be created as PRIMARY INDEX.

I have not defined PRIMARY INDEX however a column is defined as PRIMARY KEY

In this case, Teradata will make the PRIMARY KEY column as UNIQUE PRIMARY INDEX of the table.

I have defined both PRIMARY KEY and PRIMARY INDEX on different column

In this case, Teradata will make PRIMARY KEY column as UNIQUE SECONDARY INDEX i.e. UNIQUE INDEX on the table.

So one must understand the importance of PRIMARY INDEX in Teradata. Generally, PRIMARY KEY concept is taken care by UNIQUE PRIMARY INDEX in Teradata environment.

what is the difference between primary key and unique key

Similar to primary key , you can define column as UNIQUE which will enforce unique value for the column. The difference is you can have multiple columns in the table defined as UNIQUE however a table can have only one primary key.

2 thoughts on “Difference between Teradata Primary Index and Primary Key

  1. Hi Raj, Thanks a lot for this post.I'm working in Teradata to Oracle migration project.for UNIQUE PRIMARY INDEX(PSTM_ID,PSTM_ATM,TAX_ID) changed like PSTM_ID as PRIMARY KEY and PSTM_ATM ,TAX_ID both with NOT NULL UNIQUE but bit confused with PRIMARY INDEX( PSTM_ID,PSTM_ATM,TAX_ID) as PRIMARY INDEX can be NULL, UNIQUE or NON UNIQUE.How to modify the same in Oracle?
    Thanks,
    Sakthi.

    1. Hi Sakthi
      Technically, yes you can have NULL in your INDEX column however you should also consider functionally what kind of values can be expected in columns.
      Looking at the column names you have mentioned PSTM_ID,TAX_ID gives the impression as if these are id columns and perhaps the surrogate key columns. So functionally you may never have NULL as the value for such columns.

Leave a Reply

Your email address will not be published. Required fields are marked *