Teradata Renaming Columns

Renaming Columns in the Teradata Table:

Columns can be renamed in the a table using the ALTER TABLE syntax.

ALTER TABLE TABLENAME RENAME OLDCOLUMNNAME TO NEWCOLUMNNAME;

Following are the constraints while renaming the columns:

1. Column being renamed must not be a part of primary index.
    
2. Column should not be the referenced or referencing column in a referential integrity.
    
3. The New name should not already exist on the table.
    
4. The affected column is not referenced in the UPDATE OF clause of a trigger.

Caution:  The renaming of the columns does not cascade the new name to the existing macros and views.

Macros and views can be rendered unusable

Example 1:

SHOW TABLE employee

CREATE SET TABLE MY_TEST_TABLES.employee ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Employeeid INTEGER,

      DepartmentNo INTEGER,

      Salary DECIMAL(8,2),

      Hiredate DATE FORMAT 'YYYY-MM-DD',

      hike DECIMAL(10,2) NOT NULL DEFAULT 0.00 )

PRIMARY INDEX ( Employeeid )

INDEX ( DepartmentNo ) ORDER BY VALUES ( DepartmentNo );

ALTER TABLE employee rename employeeid to employee_id;

ALTER TABLE Failed. 3631:  Column EMPLOYEEID is an index column and cannot be modified. 

Example 2:

ALTER TABLE employee rename hike to hike_increase;

SHOW TABLE employee

CREATE SET TABLE MY_TEST_TABLES.employee ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Employeeid INTEGER,

      DepartmentNo INTEGER,

      Salary DECIMAL(8,2),

      Hiredate DATE FORMAT 'YYYY-MM-DD',

      hike_increase DECIMAL(10,2) NOT NULL DEFAULT 0.00 )

PRIMARY INDEX ( Employeeid )

INDEX ( DepartmentNo ) ORDER BY VALUES ( DepartmentNo );