15.10 - ALTER TABLE - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Purpose

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 ANSI Temporal Table Support, B035-1186 and Temporal Table Support, B035-1182.

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

Syntax - ALTER TABLE Basic


ALTER_TABLE_basic

Syntax - ALTER TABLE Join Index



Syntax - ALTER TABLE Revalidation



Syntax - ALTER TABLE Release Rows