ALTER TABLE SQL Statement | Teradata Vantage - ALTER TABLE - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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.

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 table.

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.
  • 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.

When you execute 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 execute 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 either of the following privileges to add a UDT column to a table or to drop a UDT column from a table:
  • 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

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.