How to increase length of a field when it's part of an indexed table?

Database

How to increase length of a field when it's part of an indexed table?

Greetings. SQL newbie here, would be grateful for any guidance you can provide. I created a table with the following command:

create table PartMaster
(
Division char(3)
,Key char(15)
,Desc char(100)
,Model char(24)
,Image char(100)
,Price decimal(9,2)
)
primary index (Division, Model)

Now I'm trying to increase the size of the Desc field to 130 characters, using the following command:

alter table PartMaster
alter Desc char(130);

Teradata is giving me an error saying "3707: Syntax error, expected something like a 'CONVERT_TABLE_HEADER' keyword between the word 'PartMaster' and the alter keyword."

Why am getting the error? How do I change the field length?

Thank you again for any guidance you can provide.

--Rick
2 REPLIES
Enthusiast

Re: How to increase length of a field when it's part of an indexed table?

Hi,

You cannot ALTER a column directly. First you will need to DROP that column and then add with the new data type.

Alter table PartMaster

Drop Desc ;

Alter table PartMaster

Add Desc Char(130);

But this will add the column at the end of the table. If you want to maintain the order

then you will need to use create and rename as below:

create table PartMaster_NEW

(

Division char(3)

,Key char(15)

,Desc char(130)

,Model char(24)

,Image char(100)

,Price decimal(9,2)

)

primary index (Division, Model);

Insert into PartMaster_NEW

Select * from PartMaster ;

Rename table PartMaster to PartMaster_old ;

Rename table PartMaster_NEW to PartMaster ;

Drop table PartMaster_old ;

Re: How to increase length of a field when it's part of an indexed table?

Thank you for the excellent guidance, indrajit_td. Much appreciated.