Add or modify the primary index of the table in Teradata using alter table query
Contents
Initially the table might be created without Primary index or Unique primary index. Later we wants to add the primary index to the existing tables.
Add the Unique primary index for the empty table in Teradata
If the table is empty,we can just add the primary index of the table using alter table query.
Syntax to Alter the Unique Primary Index
|
1 |
Alter table <table name> MODIFY unique primary index(column1,column2,..); |
Example for Alter table Unique Primary Index
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE SET TABLE BANKING_DB.CUSTOMER ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( Cust_Id INTEGER, Cust_Name CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, Join_Date DATE FORMAT 'YYYY/MM/DD', Mobile_Number INTEGER, Login_Count DECIMAL(18,0) ) NO PRIMARY INDEX; |
The table CUSTOMER is created without primary index Then we have added the Cust_id as the Unique primary index to the table using the alter table query.
|
1 |
Alter table BANKING_DB.CUSTOMER MODIFY unique primary index(Cust_Id); |
Alter the Unique primary index for non-empty table in Teradata
If the table has contains the data, Teradata will not allow to add the unique primary index of the table. We need to copy the data out to another table, volatile or permanent and then we can alter the index of the table.
Step 1 : Copy the table to another table using Create table as Select query
|
1 2 |
CREATE TABLE Database_name.Table_name_new AS (SELECT * FROM Database_name.Table_name_existing) with DATA AND STATS; |
Step 2 : Delete all the records in the table that needs to alter the primary index
|
1 |
DELETE FROM <Table name> ALL; |
Step 3: Alter the Primary index of the table using alter table query.
|
1 |
Alter table <table name> MODIFY unique primary index(column1,column2,..); |
Step 4: Copy all records from the backup table using the Insert into Select query
|
1 2 3 4 |
INSERT INTO <table name> (Column1,Column2,..) SELECT (Column1,Column2,..) FROM <Backup table name>; |
Step 5 : Drop the Backup table using drop table query
|
1 |
DROP TABLE <table name>; |
Recommended Articles
- How to create partitioned primary index in Teradata?
- How to create or drop the secondary index in Teradata?
- How to add the primary index in the create table statement using Parser?
