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
Alter table
MODIFY unique primary index(column1,column2,..);
Example for Alter table Unique Primary Index
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.
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
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
DELETE FROM
ALL;
Step 3: Alter the Primary index of the table using alter table query.
Alter table
MODIFY unique primary index(column1,column2,..);
Step 4: Copy all records from the backup table using the Insert into Select query
INSERT INTO
(Column1,Column2,..)
SELECT (Column1,Column2,..)
FROM ;
Step 5 : Drop the Backup table using drop table query
