Add one or more columns to a table or global temporary table, add or change attributes and options, including partitioning, constraints, and compression. You can also drop columns, change a join index, or revalidate a table.
To alter a foreign table, use ALTER FOREIGN TABLE.
- New attributes for one or more columns of a table or global temporary table definition.
- A set of new columns to an existing table or global temporary table definition.
- A set of columns to an existing column partition of a column-partitioned table.
- A set of columns as a new column partition for an existing column-partitioned table.
Drops one or more columns from a table or global temporary table definition.
Drops a column partition when all of its component columns are dropped.
Adds or drops the FALLBACK option for a table.
Adds or modifies the JOURNAL option for a table.
- Column-level and table-level constraints.
- Referential constraints.
- DATABLOCKSIZE or percent FREESPACE for a table or global temporary table definition.
- MERGEBLOCKRATIO for a permanent table or permanent journal table definition.
- LOG and ON COMMIT options for a global temporary table.
- Name of a column.
- Properties of the primary index for a table.
- Partitioning properties of a table, including modifications to the partitioning expression of a row-partitioned table.
- Column partitioning properties for column-partitioned tables and join indexes.
- Disk I/O integrity option for a table.
- Compression attributes for the columns of a table.
- Load isolation attribute for a table.
Regenerates table headers and optionally validates and corrects the partitioning of row-partitioned table rows.
For information about temporal tables and temporal syntax, see Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182.
ANSI Compliance
This statement is ANSI SQL:2011 compliant, but includes non-ANSI Teradata extensions.
Other SQL dialects support similar non-ANSI standard statements with names such as the following:
Required Privileges
To alter a table, you must have DROP TABLE privilege on that table or on the database containing the table.
To add or drop a row-level security column using ALTER TABLE, you must also have the CONSTRAINT ASSIGNMENT privilege in addition to DROP TABLE privilege.
- ALTER TABLE … ADD UNIQUE (column_list)
- ALTER TABLE … ADD PRIMARY KEY (column_list)
- ALTER TABLE … MODIFY WITH INSERT
- ALTER TABLE … REVALIDATE PRIMARY INDEX WITH INSERT
- UDTUSAGE privilege on the specified UDT.
- At least one of the following privileges on the SYSUDTLIB database:
- UDTUSAGE
- UDTTYPE
- UDTMETHOD
You must have the CONSTRAINT ASSIGNMENT privilege to add a row-level security constraint column to a table or drop a row-level security constraint column from a table.
Privileges Granted Automatically
- DELETE on the error table for the constraint
- DROP TABLE on the error table for the constraint
- INSERT on the error table for the constraint
- SELECT on the error table for the constraint
- UPDATE on the error table for the constraint
No privileges are granted automatically for any other ALTER TABLE option.