 |
 |
Archives of the TeradataForum
Message Posted: Mon, 16 Aug 2004 @ 10:31:05 GMT
| 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.
Regards,
Victor
| |
