15.10 - Data Definition in SQL - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

Data definition phrases, also referred to as data description phrases, are used in SQL statements to define how to store data in the columns of a table, how to present the data in the results of queries, and whether to apply column-level integrity constraints.

You can also use data definition phrases in expressions to convert data to another type or modify data attributes.

where:

 

Syntax element …

Specifies …

data_type_declaration

the data type of a column, such as BYTE or FLOAT.

For more information, see “Data Type Phrases” on page 14.

data_type_attribute

attributes for a column, such as a default value to insert when an INSERT statement omits the value.

For more information, see “Core Data Type Attributes” on page 17.

column_storage_attribute

to compress certain values and nulls for one or more columns of a table.

For more information, see:

  • “Storage and Constraint Attributes” on page 18.
  • “COMPRESS and DECOMPRESS Phrases” on page 22.
  • column_constraint_attribute

    column-level integrity constraints, such as PRIMARY KEY.

    For more information, see:

  • “Storage and Constraint Attributes” on page 18.
  • “Column and Table Constraints” on page 30.
  • “ALTER TABLE” and “CREATE TABLE” in SQL Data Definition Language.
  • The following CREATE TABLE statement uses Teradata SQL data definition phrases to define the columns of the Employee table:

    CREATE TABLE Employee
     (EmpNo PRIMARY KEY SMALLINT FORMAT '9(5)' 
       CHECK(EmpNo BETWEEN 1000 AND 32001), 
      Name VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, 
      DeptNo SMALLINT FORMAT '999' 
       CHECK (DeptNo BETWEEN 100 AND 900), 
      JobTitle VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
      Salary DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99' 
       CHECK (Salary BETWEEN 1.00 AND 999000.00),
      YrsExp BYTEINT FORMAT 'Z9' 
       CHECK (YrsExp BETWEEN -99 AND 99), 
      DOB DATE FORMAT ’MMMbDDbYYYY’ NOT NULL, 
      Sex CHAR CHARACTER SET LATIN UPPERCASE NOT NULL, 
      Race CHAR CHARACTER SET LATIN UPPERCASE, 
      MStat CHAR CHARACTER SET LATIN UPPERCASE, 
      EdLev BYTEINT FORMAT 'Z9' 
       CHECK (EdLev BETWEEN 0 AND 2) NOT NULL, 
      HCap BYTEINT FORMAT 'Z9' 
       CHECK (HCap BETWEEN -99 AND 99)
    INDEX (Name) ;
     

    The following SELECT statement uses Teradata SQL data definition phrases to modify the heading of the EmpNo column and the format of the results of the Salary column:

    SELECT EmpNo (TITLE 'Employee Number'),
      Salary (FORMAT 'GLLZ(I)D9(F)')
    FROM Employee;