You may need to change the structure or definition of an existing table or temporary table. In many cases, you can use ALTER TABLE and RENAME to make the changes. Some changes, however, may require you to use CREATE TABLE to recreate the table.
You cannot use ALTER TABLE on an error logging table.
Making Changes to a Table
Use the RENAME TABLE statement to change the name of a table, temporary table, queue table, or error logging table.
- Add or drop columns on an existing table or temporary table
- Add column default control, FORMAT, and TITLE attributes on an existing table or temporary table
- Add or remove journaling options on an existing table or temporary table
- Add or remove the FALLBACK option on an existing table or temporary table
- Change the DATABLOCKSIZE or percent FREESPACE on an existing table or temporary table
- Add or drop column and table level constraints on an existing table or temporary table
- Change the LOG and ON COMMIT options for a global temporary table
- Modify referential constraints
- Change the properties of the primary index for a table (some cases require an empty table)
- Change the partitioning properties of the primary index for a table, including modifications to the partitioning expression defined for use by a partitioned primary index (some cases require an empty table)
- Regenerate table headers and optionally validate and correct the partitioning of PPI table rows
- Define, modify, or delete the COMPRESS attribute for an existing column
- Add the BLOCKCOMPRESSION option used to modify the temperature-based Block Level Compression (BLC) for a table.
- Change column attributes (that do not affect stored data) on an existing table or temporary table
Restrictions apply to many of the preceding modifications.
- Redefine the primary index or its partitioning for a non-empty table when not allowed for ALTER TABLE
- Change a data type attribute that affects existing data
- Add a column that would exceed the maximum lifetime column count
Interactively, the SHOW TABLE statement can call up the current table definition, which can then be modified and resubmitted to create a new table.
If the stored data is not affected by incompatible data type changes, an INSERT... SELECT statement can be used to transfer data from the existing table to the new table.
Related Topics
For a complete list of rules and restrictions on using ALTER TABLE to change the structure or definition of an existing table, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.