15.10 - Column Attributes - 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

The data type attributes, column constraints, or both for a column.

You can specify the following column attributes:

  • UPPERCASE
  • CASESPECIFIC and NOT CASESPECIFIC
  • FORMAT quotestring

    If you specify FORMAT, the format string must be valid for the external type of the UDT, the external type being its fromsql transform routine as defined either by default or by user definition using the CREATE TRANSFORM statement. See CREATE TRANSFORM and REPLACE TRANSFORM for details.

    If you do not specify a format, the system automatically applies the default display format of the external type.

  • TITLE quotestring
  • NAMED name
  • DEFAULT
  • WITH DEFAULT

    The WITH DEFAULT attribute is valid for Period columns, but not for UDT or Geospatial columns.

  • CHARACTER SET server_character_set

    Character column definitions use the character set assigned as the default for the user with CREATE USER and MODIFY USER. You can explicitly override the default character set definitions by using the CHARACTER SET clause. For example, if the DEFAULT CHARACTER SET defined by CREATE USER is Unicode for a user, whenever that user creates or alters a table, the declaration CHARACTER(n) in CREATE TABLE or ALTER TABLE is equivalent to CHARACTER(n) CHARACTER SET UNICODE. See “CHARACTER SET Phrase” in SQL Data Types and Literals, B035-1143.

    You cannot specify a character server data set of KANJI1. Otherwise, the system returns an error to the requestor.

  • NULL or NOT NULL

    The default is NULL except when you copy a table definition using the CREATE TABLE … AS syntax, in which case the system carries the specification made for the source table over to the target table definition. You must specify NOT NULL if you do not want nulls to be valid for the column.

For information about converting data between data types, see SQL Functions, Operators, Expressions, and Predicates, B035-1145.

The following column attributes are supported for UDT, Geospatial, and Period columns.

  • DEFAULT NULL
  • FORMAT
  • NAMED
  • NOT NULL
  • NULL
  • TITLE

The following column attributes are not supported for UDT, Geospatial, and Period columns.

  • CASESPECIFIC
  • NOT CASESPECIFIC
  • CS
  • NOT CS
  • CHARACTER SET
  • COMPRESS
  • DEFAULT DATE
  • DEFAULT TIME
  • DEFAULT USER
  • UPPERCASE
  • UC

For a complete list of the attributes that are supported for Teradata Database, see SQL Data Types and Literals, B035-1143.

For information about the additional column attributes that apply to the columns of temporal tables, see Temporal Table Support, B035-1182 and ANSI Temporal Table Support, B035-1186.

Teradata Database supports the following column constraint forms.

  • UNIQUE
  • PRIMARY KEY
  • REFERENCES referenced_table_name
  • CHECK (boolean_condition)

You cannot specify column-level or table-level constraints for UDT columns. You cannot specify the following constraints for UDT, Geospatial, or Period columns.

  • BETWEEN … AND
  • CHECK
  • PRIMARY KEY
  • REFERENCES referenced_table_name
  • UNIQUE (boolean_condition)

UDT, Geospatial, and Period columns are not supported as Identity columns.

For more detail, see Identity Column Parameters and Column Constraint Attributes Clause.