Rules and Restrictions | ALTER TABLE | VantageCloud Lake - Rules and Restrictions for ALTER TABLE (Basic Table Parameters)

Rules and Restrictions | ALTER TABLE | VantageCloud Lake - Rules and Restrictions for ALTER TABLE (Basic Table Parameters) - Teradata VantageCloud Lake

Lake - Working with SQL

Deployment

VantageCloud

Edition

Lake

Product

Teradata VantageCloud Lake

Release Number

Published

February 2025

ft:locale

en-US

ft:lastEdition

2025-11-21

dita:mapPath

jbe1714339405530.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

jbe1714339405530

ALTER TABLE and Table Version Numbers

Any change to the structure of a table or join index increments its version number.

You cannot perform any of the following actions over a DDL request that alters table structure:

Compute cluster restore
Single AMP restore
Rollforward or rollback of a permanent journal

Number of Changes Made in One ALTER TABLE Statement

The rules for number of changes that can be made within a single ALTER TABLE statement follow:

Only one change can be made per column per ALTER TABLE statement.
Only one CHECK alteration is permitted per ALTER TABLE statement, and you cannot mix CHECK and non-CHECK alterations on the same table for the same ALTER TABLE statement.

Modifying Nonpartitioned NoPI Tables

ALTER TABLE supports nonpartitioned NoPI tables in the same manner as tables with a primary index or primary AMP index, except:

You cannot add a permanent journal to a nonpartitioned NoPI table or to a column-partitioned table definition.
You cannot add an identity column to a nonpartitioned NoPI table definition.
To alter a NoPI table to have partitioning, the table must be empty and the partitioning must include column partitioning.
To alter a NoPI table to have a primary index or primary AMP index, the table must be empty. To alter a table with a primary index to have no primary index, the table must be empty. To alter a table with a primary AMP index to have no primary index and not be partitioned, the table must be empty.

Modifying NUMBER Columns

The following rules and restrictions apply to modifying NUMBER columns in a table. These rules and restrictions apply to modifying the precision and scale of exact NUMBER columns with modifying their rows.

You can increase the precision of exact NUMBER columns.
You can increase both the precision and scale of exact NUMBER columns if you increase the precision by as much as you increase the scale.
You can change an exact NUMBER column to an approximate NUMBER column or approximate NUMBER column with scale if you do not decrease the scale.
You can change both an exact NUMBER column and an approximate NUMBER column with scale to an approximate NUMBER column without scale.
You cannot decrease the precision of an exact NUMBER column unless the table is not populated with rows.
You cannot decrease the scale of an exact NUMBER column unless the table is not populated with rows.
You cannot increase the scale of a NUMBER column unless you also increase its precision by at least as much.
You cannot modify an approximate NUMBER column to be either an exact NUMBER column or an approximate NUMBER column with scale.
You cannot modify an exact NUMBER column to be an approximate NUMBER column with a decrease in scale.

Restrictions on Modifying Queue Tables

You cannot change a queue table into a non-queue table.
You cannot modify the first column defined for a queue table to be something other than a user-defined QITS column (see QUEUE Keyword and Column Identifier).
You cannot drop the QITS column from a queue table.
You cannot modify the QITS column by adding either of the following attributes.
- UNIQUE
- PRIMARY KEY
  Therefore, you cannot add a simple UPI on the QITS column.
  
  You cannot change the QITS column to a UPI.
You cannot add either of the following constraints to a queue table definition.
- FOREIGN KEY ... REFERENCES
- REFERENCES
You cannot modify any column of a queue table to have a LOB data type. (For information about LOB data types, see BLOB Data Type and CLOB Data Type).
You cannot add a permanent journal to the definition for a queue table.
You cannot add a reference to queue table columns in a REFERENCES clause for any table.
Otherwise, all column- and table-level constraint clauses are valid within queue table definitions with the exception of UNIQUE and PRIMARY KEY constraints not being valid attributes for the QITS column.
You cannot modify a queue table definition to have a partitioned primary index.

Modifying Data Tables Defined with an Error Table

The following does not apply to the referential integrity error tables described in Referential Integrity Error Tables for Column-Partitioned Tables.

See Rules and Restrictions for Error Tables.

The system maintains the compatibility between data tables and their associated error tables by disallowing any of the operations in the following list when an error table is defined for a data table.

You cannot make any of the following changes to a data table definition if the table is associated with an error table.
- Add a column
- Drop a column
- Alter its primary index
- Change its fallback protection
You can modify the partitioning of a partitioned data table that has an associated error table.

Large Object Data Types

Vantage supports the following large object (LOB) data types.

Binary large object (BLOB)
Character large object (CLOB)

CLOB LATIN/UTF16 is only supported on the Block File System on the primary cluster. It is not available for the Object File System.

The rules for specifying these types in an ALTER TABLE request are different from the rules that apply to other data types. Rules for specifying LOB data types in an ALTER TABLE statement are as follows.

You can increase the maximum size for a BLOB or CLOB column definition, but you cannot decrease that size.
To increase the maximum size for a BLOB or CLOB column definition, use the ADD column_name option, specify the name of the column whose LOB data type you want to increase, and Vantage modifies the size of the type to the value you specify. For example, suppose you want to double the maximum size of a CLOB column named standard_error in the table named performance_data from 524,288,000 characters to 1,048,576,000 characters. You can use the following ALTER TABLE request to increase the size of the CLOB data type for standard_error to 1,048,576,000 characters.
```
ALTER TABLE performance_data
ADD standard_error CLOB(1048576000);
```
Vantage knows that column standard_error already exists in performance_data, and therefore interprets the ADD option to mean that standard_error is to be modified, not added.
You can only alter the following column attributes for a BLOB or CLOB column.
- NULL
- NOT NULL
  You cannot change the attribute from NULL to NOT NULL if there are nulls in any rows in the table for that column.
- TITLE
- FORMAT
You can add BLOB and CLOB columns to a maximum of 32 per base table definition.
If a table already has 32 BLOB or CLOB columns or a mixture of both, you cannot add another BLOB or CLOB column to the table using the same ALTER TABLE request that drops one of the 32 existing BLOB or CLOB columns.
Instead, you must drop an existing BLOB or CLOB column with one ALTER TABLE request and then add the new BLOB or CLOB column using a separate ALTER TABLE request.
You can drop BLOB or CLOB columns from a base table.
If you drop all BLOB and CLOB columns from a base table, that table is no longer bound to any BLOB or CLOB restrictions.
A constraint definition can neither be defined for nor reference a BLOB or CLOB column.
You cannot specify expressions or referenced columns with BLOB or CLOB data types in a start or end expression for a primary index range.