ALTER TABLE SQL Statement | VantageCloud Lake - ALTER TABLE - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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, revalidate a table, or change an empty Block File System table to an Object File System table.

Adds any of the following items:
  • 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 Block File System table. For Object File System tables, FALLBACK has a default setting that you cannot change.

Adds or modifies the JOURNAL option for a table.

Modifies the following:
  • Column-level and table-level constraints.
  • Referential constraints.
  • DATABLOCKSIZE or percent FREESPACE for a table or global temporary table definition on the Block File System. For Object File System tables, DATABLOCKSIZE and FREESPACE have default settings that you cannot change.
  • MERGEBLOCKRATIO for a permanent table or permanent journal table definition on the Block File System. For Object File System tables, MERGEBLOCKRATIO has a default setting that you cannot change.
  • 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.

Regenerates table headers and optionally validates and corrects the partitioning of row-partitioned table rows.

For ALTER TABLE parameters and options, see the following topics:
Also see the following sections:

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.

When you run either of the following ALTER TABLE forms, you must also have the INDEX privilege on the table:
  • ALTER TABLE … ADD UNIQUE (column_list)
  • ALTER TABLE … ADD PRIMARY KEY (column_list)
When you run any of the following ALTER TABLE forms, you must also have the INSERT privilege on save_table:
  • ALTER TABLE … MODIFY WITH INSERT
  • ALTER TABLE … REVALIDATE PRIMARY INDEX WITH INSERT

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

ALTER TABLE ADD CONSTRAINT, where the added constraint is a referential integrity constraint, grants the following privileges automatically to the requestor adding the RI constraint:
  • 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.