17.00 - PRIMARY INDEX - 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)

A primary index defined on the join index. The primary index for an aggregate join index must be drawn from the columns specified in the GROUP BY clause of the join index definition. You cannot specify any aggregated columns as part of the primary index. If the primary index is not compressed, you can define it as either a single-level or multilevel row partitioned primary index by specifying one or more partitioning expressions. See PARTITION BY. You cannot partition the primary index of a join index if the index is defined with compression.

The following rules apply to specifying a row partitioned primary index for a join index.
  • The partitioning columns for an aggregate join index must be drawn from the columns specified in the GROUP BY clause of the join index definition.
  • You cannot specify an aggregated column as a partitioning column for the PPI.
  • The partitioning level in a join index acts as a constraint on its underlying base tables. If an insert, delete, or update operation on a base table causes a partition violation in the join index by making one or more of the partitioning expressions evaluate to null, the request returns an error message, and neither the base table nor the join index is updated.
  • If a join index definition includes an outer join, deleting base table rows might cause inserts into that join index for the unmatched rows.
  • For your partitioned join indexes, you must define partitioning levels that do not prevent rows from being inserted into, updated, or deleted from the base tables when required.
  • If you do not define an explicit NUPI, the first column defined for the join index is assigned to be the NUPI by default. You can define an uncompressed join index with a partitioned primary index. However, the primary index cannot be partitioned if the join index is compressed.
  • Each NUSI counts toward the maximum number of 32 secondary indexes that you can define on a join index. Each multicolumn NUSI defined with an ORDER BY clause counts as two consecutive indexes against the limit of 32 per join index.
You cannot define the primary index for a join index on a column with any of the following data types:
  • BLOB-based UDT
  • CLOB-based UDT
  • XML-based UDT
  • VARIANT-TYPE
  • ARRAY/VARRAY
  • XML
  • Geospatial
  • Derived Period
  • Period
UNIQUE
You can define a unique primary index for an uncompressed single-table join index, but the primary index for any other join index must be nonunique even when it is defined on a unique column.
index_name
The optional name of the primary being defined. For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
(primary_index_column)
Names of the primary index columns. For a composite primary index, primary_index_column indicates a comma-separated list of all the index columns in parenthesis. You cannot specify the begin or end columns of a derived period column in a primary index.

You cannot alter a table to have a row-level security constraint column as a component of its primary index.
You cannot define a primary index on a column defined with the JSON data type.