How to name the Primary indexes in Teraata table with examples
Contents
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)
|
1 2 3 4 |
CREATE TABLE Table_Name( ............. .........) PRIMARY INDEX Index_Name(Column1,Column2....); |
Option 2: (Primary Index without name)
|
1 2 3 4 |
CREATE TABLE Table_Name( ............. .........) PRIMARY INDEX (Column1,Column2....); |
Example for Primary Index
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE SET TABLE BANKING_DB.EMPLOYEE,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( Employee_Id INTEGER, Department_Id INTEGER, Employee_Name CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, Join_Date DATE FORMAT 'YYYY/MM/DD', Mobile_Number INTEGER ) PRIMARY INDEX Combined_Id ( Employee_Id,Department_Id ); |
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.
Recommended Articles
- How to add a primary key in the Create table statement using Parser?
- How to create a partitioned primary index in Teradata?
- How to create or drop the secondary index in Teradata?
