15.00 - Rules for Primary Indexes and Their Defaults for a Column-Partitioned Table - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Rules for Primary Indexes and Their Defaults for a Column‑Partitioned Table

  • A column‑partitioned table cannot have a primary index.
  • If you specify PRIMARY INDEX and a PARTITION BY clause in your CREATE TABLE request, you cannot specify a COLUMN partitioning level in the PARTITION BY clause.

    You can optionally specify a NO PRIMARY INDEX clause for a column‑partitioned table, but you cannot specify a PRIMARY INDEX (column_list) clause.

  • If you do not specify a PRIMARY INDEX clause or if you do specify a NO PRIMARY INDEX clause and you also specify a PARTITION BY clause, the partitioning must specify a COLUMN partitioning level.
  • If you do not specify a PRIMARY INDEX clause, a NO PRIMARY INDEX clause, or a PARTITION BY clause, the default primary index or the default of NO PRIMARY INDEX is determined by the setting of the PrimaryIndexDefault parameter.
  • If you specify neither PRIMARY INDEX (column_list) nor NO PRIMARY INDEX explicitly in your CREATE TABLE request, but do specify a PARTITION BY clause, then Teradata Database creates the table without a primary index regardless of the setting of the DBS Control parameter PrimaryIndexDefault (for details and exceptions, see Database Design and Utilities: Volume 1 (A-K)).
  • The default specification for the primary index of a column‑partitioned table is NO PRIMARY INDEX regardless of the setting of the DBS Control parameter PrimaryIndexDefault (see Utilities: Volume 1 (A-K) for details).
  • PrimaryIndexDefault only determines the default primary index or default NO PRIMARY INDEX if you do not specify any of the following clauses.

  • PRIMARY INDEX
  • NO PRIMARY INDEX
  • PARTITION BY