alter table modify primary index to unique primary index

UDA

alter table modify primary index to unique primary index

Hi,

I'm new to teradata.

I want to alter the PRIMARY INDEX of a table to UNIQUE PRIMARY INDEX. How to acheive this?

Also I would like to know the full alter table syntax options of Teradata.
7 REPLIES

Re: alter table modify primary index to unique primary index

Hi,

U can copy all the contents of a table into a new table and define unique primary index on the required column and later drop the old table(provided u have both create and drop table permissions)

Syntax would be,

CREATE TABLE NEW_TABLE AS
( SELECT *
FROM OLD_TABLE
)UNIQUE PRIMARY INDEX(REQUIRED_COLUMN)
WITH DATA;

DROP TABLE OLD_TABLE;

I am not very sure whether it is making any sense or not.

You can find alter table syntax in Teradata documentation(SQL Reference:Data Defination statements)

If u want, i can mail u a copy of that.

Teradata Employee

Re: alter table modify primary index to unique primary index

Hi Ramvenky,
Here you have to consider few things.

You can always change the UNIQUE PRIMARY INDEX to PRIMARY INDEX no matter the table is populated or not using the following statement

ALTER TABLE Tabla_Name MODIFY NOT UNIQUE PRIMARY INDEX (Column_Name)

But if you want to change the PRIMARY INDEX to UNIQUE PRIMARY INDEX then table must be empty i.e. there should not be any row in the table. Use the following query to change the PI in case the table is empty

ALTER TABLE Tabla_Name MODIFY UNIQUE PRIMARY INDEX (Column_Name)

Following are the statements to change the NUP to UPI without loosing data

CREATE TABLE NEW_TABLE
,FALLBACK/NO FALLBACK
(Columns1 Data_Type,
Column2 DataType
.
.
)
UNIQUE PRIMARY INDEX(REQUIRED_COLUMN);

And Then

INSERT INTO New_Table_Name SEL * FROM Old_Table_Name;

Then Delete the old Table

Drop Old_Table

Rename New_Table
N/A

Re: alter table modify primary index to unique primary index

There's no reason to copy to a new table.

Simply add a USI on the NUPI columns and then
ALTER TABLE xxx MODIFY UNIQUE PRIMARY INDEX (NUPI_cols)

This also drops the USI automatically.

Dieter

Re: alter table modify primary index to unique primary index

I am intypical situation , there is table of 3 TB and the table is not partitation yet.

After looking at data in table, I came to conclussion partition on date is best fit.

but here is the problem:-

table layout

CREATE TABLE ORDERS

ORNUM INTEGER,

ORNAME VARCHAR,

CREAT_TS TIMESTAMP(6))

PRIMARY INDEX PIE(ENO)

--

CREAT_TS IS NOT DEFINED IN PRIMARY INDEX, upto my knowledge unless we have that field in PI , I can not create the partation.

I create a temp table 

CREATE TABLE ORDERS

(

ORNUM INTEGER,

ORNAME VARCHAR,

CREAT_TS TIMESTAMP(6))

PRIMARY INDEX PIE(ORNUM,CREAT_TS)

PARTITION  BY RANGE_N(CAST(CREAT_TS AS DATE) BETWEEN '2005-01-01' AND '2013-02-10' EACH INTERVAL '1' MONTH)

TRYING TO insert that data into temp table , but it is failing due to spool, so i decided to move year wise data like -CREAT_TS  <= '2011-09-23 13:35:57.83' .

can some help , can i partition main table directly .. i tried with alter statments but it is not working .. 

Re: alter table modify primary index to unique primary index

Please consider your primary index again.

Partitioning columns do not have to be columns in the primary index. If they aren't, then the primary index cannot be unique.

1) If you will be joining your order table with other table based on ornum then let the PI be ornum and let it be nupi, and partition it by creat_ts. 

2) If the temp table and order table has the same PI then data of both tables will be on hte same AMP and it will not spool out.

I don't think there is any way to partiton the table without creating a new temp table.

Limitations with ALTER TABLE:

• Primary Index of a non-empty table may not be altered

• Partitioning of a non-empty table is generally limited to altering the “ends”.

Re: alter table modify primary index to unique primary index

HI Dieter,

Could you please explain on your comment. As if in any case we have to have the table empty then there is no use of these workarounds. Please confirm the steps.

I have created the table with a Non unique primary index and then i inserted one row. Afterwards i defined one secondary index(USI) then the alter command to change it to Unique primary index. It gives me error that the table has some data

N/A

Re: alter table modify primary index to unique primary index

Thank you Dieter! SImple and elegant :)

mmongia08 what is to explain, just create a UNIQUE secondary index on the same column(s) as the PI