How to name the Primary indexes in Teraata table with examples

Primary Index in Teradata

While we create the table in Teradata, we can specify the Primary index(PI) or Unique primary Index(UPI) or No Primary index in the DDL. The primary index is used to distribute the data across the AMP. When we query the data from the table, the index helps to access the specific AMP and retrieve the data faster. Also It allows duplicates and NULL values in the table.

Syntax for the primary index

If we specify more than one primary index for a table, we can give the common name to all the primary index. In case if we want to delete all the primary index, we can simply use the primary index name and remove it from the table.

Option 1: (Naming the Primary Index)

Option 2: (Primary Index without name)

Example for Primary Index

Here we are creating the table Employee with the primary index of columns Employee_Id and Department_Id. Since Teradata allows to name the primary index,we have named it as Combined_Id.

If we don’t want to give the name for the primary index ,we can simply create the primary index with the columns alone as below.

PRIMARY INDEX (Employee_Id,Department_Id );

Allowed duplicates and Null values in the primary index

The Employee table contains the duplicates and NULL values in the Primary index columns(Employee_Id & Department_Id) as below.

Example for primary index in Teradata

Recommended Articles