17.00 - Example: UDT Primary and Secondary Index Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

This example set demonstrates how you can create a table that specifies a UDT column in a primary or secondary index or both.

First define the UDTs that are used for the example columns.

UDT tbl_integer is a distinct UDT based on the INTEGER data type.

     CREATE TYPE tbl_integer AS INTEGER FINAL;

UDT tbl_char50 is also a distinct UDT and is based on the CHARACTER data type.

     CREATE TYPE tbl_char50 AS CHARACTER(50) FINAL;

EXSP_STRUCTURE_LOB is a structured UDT based on the INTEGER and CLOB data types.

     CREATE TYPE EXSP_STRUCTURE_LOB AS (
       age INTEGER, 
       c1  CLOB(512), 
       c2  CLOB(512), 
       cnt INTEGER, 
       c3  CLOB(512))
     NOT FINAL
     CONSTRUCTOR METHOD exsp_structure_lob(
       file1 VARCHAR(20), 
       file2 VARCHAR(20),
       file3 VARCHAR(20))
     RETURNS exsp_structure_lob
     SPECIFIC lobconstructor
     SELF AS RESULT
     NO SQL
     PARAMETER STYLE TD_GENERAL
     DETERMINISTIC
     LANGUAGE C;

The first example table defines its UPI on the distinct UDT column id.

     CREATE TABLE table_1 (
       id       tbl_integer, 
       emp_name tbl_char50) 
     UNIQUE PRIMARY INDEX(id);

The second example table defines its NUPI on the distinct UDT column id.

     CREATE TABLE table_2 (
       id       tbl_integer, 
       emp_name tbl_char50) 
     PRIMARY INDEX(id);

The third example table defines a USI on the distinct UDT column emp_name.

     CREATE TABLE table_3 (
       id       tbl_integer, 
       emp_name tbl_char50) 
     UNIQUE INDEX(emp_name);

The fourth example table defines a NUSI on the distinct UDT column emp_name.

     CREATE TABLE table_4 (
       id         tbl_integer, 
       emp_name   tbl_char50, 
       start_date DATE) 
     INDEX(emp_name);

The fifth example table defines a composite NUSI on the distinct UDT column emp_name and the DATE column start_date.

     CREATE TABLE table_5 (
       id         tbl_integer, 
       emp_name   tbl_char50, 
       start_date DATE) 
     INDEX(emp_name, start_date);