Teradata : Alter tables

Pradeep

Teradata : Alter tables

This tutorials will explain how to alter tables in Teradata. This alteration may not be limited to addition of new column, drop existing column or rename column.

Rename the table
Add / Modify / Rename / Drop Column
Add / Remove Column Title
Add Column Compression
Remove Column Compression
Add/ Modify / Remove Primary Index
Add / Remove NOT NULL Constraint
Add / Remove Primary Key

➠ Restrictions on altering a table

Length of the datatype cannot be reduced and user can only increase the length of datatype. Users will get below error if they try to reduce length.
```
*** Failure 3558 Cannot alter the specified attribute(s) for column_name
```
Datatype of columns cannot be changed if old datatype of column and new datatype of column are not compatible. Users will get below error if they try to change datatype.
```
*** Failure 3558 Cannot alter the specified attribute(s) for column_name
```
Primary index can be changed only for an empty table. Users will get below error if they try to change primary index of populated table.
```
*** Failure 5735 The primary index columns may not be altered for a nonempty table.
```
Column must be 'NOT NULL' to define primary key on that column of table, otherwise it will fail with below error.
```
*** Failure 5323 The PRIMARY KEY column 'order_id' must be NOT NULL.
```

➠ Table for Examples: Lets create below table for demonstrating alter commands.


CREATE TABLE teradataAlterTest
(  
   pi INTEGER,
   order_id INTEGER,
   order_date DATE,
   cust_id DECIMAL(10),
   order_status VARCHAR(50)
)primary index(pi);

➠ Various examples of altering tables in Teradata

Rename the table

Renaming the existing table


RENAME TABLE teradataAlterTest TO teradataAlterTest1;

Add / Modify / Rename / Drop Column

Adding new column to the existing table


ALTER TABLE teradataAlterTest ADD active_ind CHAR(1) CHARACTER SET LATIN CASESPECIFIC;

Increase length of existing column in the table i.e modify column datatype from varchar(50) to varchar(100)
```
ALTER TABLE teradataAlterTest ADD order_status VARCHAR(100);
```

Renaming existing column in the table


ALTER TABLE teradataAlterTest RENAME active_ind TO delete_ind;

Dropping existing column in the table


ALTER TABLE teradataAlterTest DROP delete_ind;

Adding new column with same name as existing but incompatible datatype.


ALTER TABLE teradataAlterTest RENAME active_ind TO delete_ind, ADD active_ind INTEGER;

ALTER TABLE teradataAlterTest DROP delete_ind;

Add / Remove Column Title

Adding column title to the existing column in the table


ALTER TABLE teradataAlterTest ADD active_ind INTEGER TITLE 'Active indicator';

Remove column title of the existing column in the table


ALTER TABLE teradataAlterTest ADD active_ind INTEGER TITLE '';

Add / Remove Compression

Adding commpression to the existing column of the table


Example 1:  ALTER TABLE teradataAlterTest ADD active_ind COMPRESS;  --Compresses all null values

Example 2:  ALTER TABLE teradataAlterTest ADD active_ind COMPRESS NULL;  --Compresses all null values

Example 3:  ALTER TABLE teradataAlterTest ADD active_ind COMPRESS(0,1);  --Compresses 0 & 1

Example 4:  ALTER TABLE teradataAlterTest ADD order_status COMPRESS ('CANCELED','CLOSED','COMPLETE'); --Will add/modify the compression of column with the passed list

Remove commpression from an existing column of the table


ALTER TABLE teradataAlterTest ADD active_ind NO COMPRESS;

Modify / Remove Primary Index

Adding/modifying primary index of the existing table, this will only work if table will be empty
```
ALTER TABLE teradataAlterTest MODIFY PRIMARY INDEX(order_id);
```
Remove primary index of the existing table, this will only work if table will be empty
```
ALTER TABLE teradataAlterTest MODIFY NO PRIMARY INDEX;
```

Add / Remove NOT NULL Constraint

Adding not null constraint to the existing column of a table
```
ALTER TABLE teradataAlterTest ADD active_ind NOT NULL;
```
Removing not null constraint from an existing column of a table
```
ALTER TABLE teradataAlterTest ADD active_ind NULL;
```

Add / Remove Primary Key

Adding primary key to the existing table, column must be 'NOT NULL' to define primary key
```
ALTER TABLE teradataAlterTest ADD PRIMARY KEY(active_ind);
```

Removing primary key from the existing table.


DROP INDEX(active_ind) ON teradataAlterTest;