15.00 - General Usage Rules - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

General Usage Rules

The following general rules apply to table definitions copied using an AS clause in CREATE TABLE.

  • When you use a subquery as your source definition, if you do not explicitly specify a column set to define the primary index, primary key, or a unique constraint for the target table, then the first column defined for the table is selected to be its non‑unique primary index by default (see “About Primary‑Indexed Tables, Row‑Partitioned Tables, Column‑Partitioned Tables, NoPI Tables, and Secondary Indexes” on page 573).
  • When you specify a subquery as your source definition, a column that maps to an expression assumes the data type of the expression result.
  • When you specify a subquery as your source definition and also specify the WITH DATA AND STATISTICS option, a special set of rules applies. See “Specific Rules For AS … WITH DATA AND STATISTICS That Uses A Subquery To Define The Source Table Column And Index Set” on page 665.
  • When you specify an existing table (not as defined by a derived table subquery) as the source table definition in the AS clause, then the new table always assumes the following table‑level characteristics of the source table.
  • Column structures, including columns with UDT, Period, ARRAY, and VARRAY types.
  • Fallback options
  • Journaling options (except when the new table is a global temporary or volatile table, in which case permanent journaling is not copied).
  • All defined indexes except hash and join indexes.
  • Note that if new indexes are defined for the target table, then that table does not assume any existing indexes.

  • When you specify an existing table (that is not specified as a subquery) as the source table definition in the AS clause and also specify the WITH DATA AND STATISTICS option, a special set of rules applies. See “Specific Rules For CREATE TABLE AS … WITH DATA AND STATISTICS That Does Not Use a Subquery To Define The Source Table Column And Index Set” on page 665.
  • If a partitioned primary index definition is copied to a new target table and no index definitions are specified for the target table, then that table is partitioned in the same way as the source table.
  • If a partitioned primary index definition is copied to a new target table, no index definitions are specified, and the CREATE TABLE request for the target table renames the partitioning columns, then those columns are also renamed in the partitioning expression for the target table.
  • Column definitions can contain any of the following attributes.
  • Column names
  • Default values
  • NOT NULL constraints
  • CHECK constraints
  • UNIQUE constraints
  • Columns in the target table are in the same left-to-right order as they are defined in the source table or source subquery select list.