15.00 - NO PRIMARY INDEX Option - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

NO PRIMARY INDEX Option

The following rules apply to the NO PRIMARY INDEX option.

  • If you specify NO PRIMARY INDEX without altering partitioning and the table currently has a partitioned primary index, Teradata Database aborts the request and returns an error to the requestor.
  • If you specify NO PRIMARY INDEX for a SET table with a primary index, Teradata Database also alters the table to be MULTISET.
  • If you specify both NO PRIMARY INDEX and NOT PARTITIONED and the table currently has neither a primary index nor is partitioned, Teradata Database aborts the request and returns an error to the requestor.
  • If you specify NO PRIMARY INDEX with DROP RANGE#n, ADD RANGE#n clauses, or both, and the table currently has a partitioned primary index, Teradata Database aborts the request and returns an error to the requestor.
  • You can specify an ADD clause for column partitioning, a RANGE_N partitioning expression, or a CASE_N partitioning expression.
  • If a row partitioning is not based on a RANGE_N or CASE_N partitioning expression, you cannot specify an ADD clause. Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • You cannot specify ALL with PRIMARY INDEX.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • If you specify COLUMN partitioning in a PARTITION BY clause, one of the following things must also be true.
  • You also specify NO PRIMARY INDEX.
  • You specify neither PRIMARY INDEX nor NO PRIMARY INDEX and the current table definition does not have a primary index.
  • If neither of these conditions is true, Teradata Database aborts the request and returns an error to the requestor.

  • If you specify NO PRIMARY INDEX for a column‑partitioned table, but you do not alter the partitioning of the table, and the table currently does not have a primary index, Teradata Database aborts the request and returns an error to the requestor.
  • You cannot specify a COLUMN partitioning level more than once in a PARTITION BY clause.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • If you specify NO AUTO COMPRESS for a partitioning level, you must specify the entire text string. NO COMPRESS is not valid for a partitioning level.
  • If you attempt to specify NO COMPRESS for a partitioning level, Teradata Database aborts the request and returns an error to the requestor.

  • You can only specify an ORDER BY clause with the NO PRIMARY INDEX option.
  • You cannot specify an ORDER BY clause with the PRIMARY INDEX option. Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • You can only specify one column name as the argument for an ORDER BY clause.
  • If you do not specify either HASH or VALUES as the argument for an ORDER BY clause, you must specify a column name delimited by parentheses.