Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 16 Aug 2004 @ 10:31:05 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: How to alter the columns in tables?
From:   Victor Sokovin

  In Teradata, how can I do following things -  

  1) How can I increase the length of the column with datatype CHAR, INTEGER or FLOAT? The ADD command works only for the VARCHAR datatype & not for others. Is there any straightway SQL for this?  

Not on the same table. You'll have to define a new table, populate it as INSERT ... SELECT from the old table, then drop the old tables and rename the new one. Make sure to check the grants!

All restictions in ALTER TABLE are listed (and it's a long list!) in SQL Reference Data Definition Statements.

  2) How can I add a not null column to a table already having data? If I specify the default value, can I take it off later on? like If say - alter table xyz ADD COL1 VARCHAR(40) NOT NULL default 'xx';  

  Once my table is altered with 'xx' values in the new column for all the existing records. How can I remove this default constraint for the New record Inserts? So that, next time if any record is being Inserted with no data in this column, it throws error instead of simply inserting the record with 'xx' value in it.  

The following SQL should do it:

alter table xyz ADD COL1 VARCHAR(40) NOT NULL default NULL ;

It does look strange, though. If you opt for rebuilding the table following the procedure described above perhaps you could just omit the DEFAULT clause on COL1. The new definition will be easier to understand.



  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023