15.10 - Column Attributes and Options - Teradata Administrator

Teradata Administrator User Guide

prodname
Teradata Administrator
vrm_release
15.10
category
User Guide
featnum
B035-2502-035K

A new row can be added to the column definition grid immediately above the row containing the cursor by pressing Ctrl-Insert.

A row can be deleted from the column definition grid by pressing Ctrl-Delete.

 

Table 14: Column Attributes and Options 

Field or Control

Description

Column Name

Specifies the name for each column of the new table.

Data Type

Specifies the data type for each column of the new table.

Size

Specifies any integer value that the data type selection requires. The size specification determines the number of characters, or numeric precision, of the column.

For data types other than DECIMAL, type a single number in the following range:

  • CHAR 1 to 65000 Number of characters in the string.
  • FLOAT 1 to 54 The Teradata Database does not use it.
  • TIME 0 to 6 Number of digits used for fractional seconds.
  • INTERVAL 1 to 4 Number of digits in first (or only) part of Interval.
  • For DECIMAL columns, the size specification can be two numbers separated by a comma. In this case, the first number specifies the total number of digits, and the second number specifies the number of digits to the right of the decimal point.

    The format for the DECIMAL size specification is:

    n,m

    Where:

    n is between 1 and 38

    m is less than or equal to n

    Char Set

    Specifies the character set to be used in this column.

    If this field is left blank, the database uses the default character set defined for your user id.

    This column applies only to CHAR, VARCHAR, LONG VARCHAR, and CLOB data types.

    Format

    Specifies the Teradata SQL FORMAT phrase that controls the display of expressions and column data.

    For example, a numeric field can be formatted as follows:

    zz,zz9.99

    A date field can be formatted as follows:

    mm/dd/yyyy

    Default

    Specifies the Teradata SQL DEFAULT control phrase, which is the value (constantvalue) to insert, by default, in the column when a specific value is not provided in an inserted row.

    Title

    Specifies the Teradata SQL TITLE phrase that defines the column heading for printed or displayed reports. The column title is not necessarily the same as the column name, which is used as a default if a title is not specified.

    Compress

    Specifies the Teradata SQL COMPRESS phrase that compresses certain column values to zero space.

    Enter either a specific value to be compressed or enter NULL to specify that NULLs be compressed.

    Multiple values separated by commas can be entered.

    Check

    Specifies the Teradata CHECK constraint for the column.

    Enter the check specification as a Boolean conditional expression using a comparator (>, >=, =, <, <=, <>) followed by a value, as in:

    >=0

    String multiple check conditional expressions together using “and” or “or”. In this case, precede all additional check specifications with the column name. The following specification, for example, specifies that column Score be between 0 and 100:

    >=0 and Score <=100

    References

    Specifies the Teradata REFERENCES attribute for the column. Enter the References specification as a table name that can be optionally:

  • Prefixed by a database name and a period.
  • Followed by a column name in parentheses.
  • For example:

    MyDatabase.MyTable(MyColumn)

    Compress UDFs

    Enter the names of the COMPRESS and DECOMPRESS functions, separated by a comma, for use in the algorithmic compression. This applies only to the CHAR, BYTE, and LOB columns in Teradata Database 13.10 and higher.

    Not Null

    Applies the Teradata SQL NOT NULL constraint specification to the table column. The NOT NULL specification ensures that every row in the table includes a value for the column.

    Note: Clearing the Not Null check box disables the Primary Key and Unique check boxes.

    UpperCase

    Enables the Teradata SQL UPPERCASE option for the column. The UPPERCASE option converts characters to uppercase for comparison and sorting operations.

    Note: The UpperCase check box and the Case Specific check box are mutually exclusive. Select either, but not both.

    Valid Time

    Specifies the VALIDTIME attribute for the column. This attribute can only be specified for columns with PERIOD (DATE) or PERIOD (TIMESTAMP) data type. Only one column can be selected for Valid Time.

    This option specifies that the table being created is a temporal table. See the CREATE TABLE section of SQL Data Definition Language for more information about temporal tables.

    Note: This attribute is only available when connected to Teradata Database version 13.10 or higher.

    With Default

    Applies the Teradata SQL WITH DEFAULT constraint specification to the table column. The WITH DEFAULT specification inserts the system-defined default value in the column whenever a value is not included with an inserted row.

    Note: The With Default check box is mutually exclusive with any entry in the Default column.

    Case Specific

    Enables the Teradata SQL CASESPECIFIC option for the column. The CASESPECIFIC option compares data characters in the same case in which they were entered.

    Note: The Case Specific check box and the UpperCase check box are mutually exclusive. Select either, but not both.

    Transaction Time

    Specifies the TRANSACTIONTIME attribute for the column. This attribute can only be specified for columns with PERIOD (TIMESTAMP(6) WITH TIME ZONE) data type. Only one column can be selected for Transaction Time. When this attribute is selected, Not Null is automatically selected.

    This option specifies that the table being created is a temporal table. See the CREATE TABLE section of SQL Data Definition Language for more information about temporal tables.

    Note: This attribute is only available when connected to Teradata Database version 13.10 or higher.

    Primary Key

    Specifies that the column is used as the primary key to the table.

    Selecting the Primary Key check box automatically selects the Not Null check box and clears the Unique check box.

    Note: The Primary Key and the Unique check boxes are mutually exclusive, and both require the Not Null check box to be selected.

    Unique

    Specifies that no two rows can have the same value in this column.

    Note: The Primary Key and the Unique check boxes are mutually exclusive, and both require the Not Null check box to be selected. Selecting the Unique check box automatically selects the Not Null check box and clears the Primary Key check box.

    Identity (Always)

    Specifies the corresponding column is assigned the IDENTITY attribute. This attribute adds the phrase 'GENERATED ALWAYS AS IDENTITY' to the column definition.

    To define an identity column, the data type must be a 4 byte (or less) numeric type which include BYTEINT, DECIMAL (18 or less), INTEGER, NUMERIC (18 or less), and SMALLINT. No other types are valid, including a UDT that is based entirely on one (distinct UDT) or several (structured UDT) of these types.

    Identity (Default)

    When selected, the corresponding column is assigned the IDENTITY attribute. This attribute adds the phrase 'GENERATED BY DEFAULT AS IDENTITY' to the column definition.

    To define an identity column, the data type must be a 4 byte (or less) numeric type which include BYTEINT, DECIMAL (18 or less), INTEGER, NUMERIC (18 or less), and SMALLINT. No other types are valid, including a UDT that is based entirely on one (distinct UDT) or several (structured UDT) of these types.