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.
*** Failure 3558 Cannot alter the specified attribute(s) for column_name
*** Failure 3558 Cannot alter the specified attribute(s) for column_name
*** Failure 5735 The primary index columns may not be altered for a nonempty table.
*** Failure 5323 The PRIMARY KEY column 'order_id' must be NOT NULL.
CREATE TABLE teradataAlterTest
(
pi INTEGER,
order_id INTEGER,
order_date DATE,
cust_id DECIMAL(10),
order_status VARCHAR(50)
)primary index(pi);
RENAME TABLE teradataAlterTest TO teradataAlterTest1;
ALTER TABLE teradataAlterTest ADD active_ind CHAR(1) CHARACTER SET LATIN CASESPECIFIC;
ALTER TABLE teradataAlterTest ADD order_status VARCHAR(100);
ALTER TABLE teradataAlterTest RENAME active_ind TO delete_ind;
ALTER TABLE teradataAlterTest DROP delete_ind;
ALTER TABLE teradataAlterTest RENAME active_ind TO delete_ind, ADD active_ind INTEGER;
ALTER TABLE teradataAlterTest DROP delete_ind;
ALTER TABLE teradataAlterTest ADD active_ind INTEGER TITLE 'Active indicator';
ALTER TABLE teradataAlterTest ADD active_ind INTEGER TITLE '';
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
ALTER TABLE teradataAlterTest ADD active_ind NO COMPRESS;
ALTER TABLE teradataAlterTest MODIFY PRIMARY INDEX(order_id);
ALTER TABLE teradataAlterTest MODIFY NO PRIMARY INDEX;
ALTER TABLE teradataAlterTest ADD active_ind NOT NULL;
ALTER TABLE teradataAlterTest ADD active_ind NULL;
ALTER TABLE teradataAlterTest ADD PRIMARY KEY(active_ind);
DROP INDEX(active_ind) ON teradataAlterTest;