Difference between Teradata Primary Index and Primary Key

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. So below are few differences between PRIMARY KEY and PRIMARY INDEX:

PRIMARY KEY

PRIMARY INDEX

1

PRIMARY KEY cannot be NULL

PRIMARY INDEX can be NULL

2

PRIMARY KEY is not
mandatory in Teradata

PRIMARY INDEX is mandatory
In Teradata

3

PRIMARY KEY does not help
in data distribution.

PRIMARY INDEX helps in data
distribution.

4

PRIMARY KEY should be
unique.

PRIMARY INDEX can be UNIQUE
(Unique Primary Index)

or NON UNIQUE (Primary Index).

5

PRIMARY KEY is logical
implementation.

PRIMARY INDEX is physical
implementation.

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

PRIMARY KEY and PRIMARY INDEX in TERADATA

PRIMARY KEY and PRIMARY INDEX in TERADATA

a) 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.

b) 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.

c) 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.