Rules and Limitations for NoPI and Column‑Partitioned Tables
The rules and limitations for NoPI tables and any type of column-partitioned table
are the same as those for primary‑indexed tables with the following exceptions:
You cannot create a nonpartitioned NoPI join index.
You can create a column‑partitioned join index (with or without row partitioning).
You cannot create a NoPI or column-partitioned:
Queue table
Error table
SET table
The default table type for NoPI and column‑partitioned tables in both Teradata and
ANSI/ISO session modes is always MULTISET.
Global temporary trace tables
Global temporary trace tables do not have a primary index or a primary AMP index by
default; however, you are not allowed to specify the NO PRIMARY INDEX option when
you create a global temporary table.
If none of the clauses PRIMARY INDEX (column_list), PRIMARY AMP INDEX, NO PRIMARY INDEX, or PARTITION BY are specified explicitly in
a CREATE TABLE or CREATE JOIN INDEX request, whether the table or join index is created
with or without a primary index or primary AMP index generally depends on whether
a PRIMARY KEY or UNIQUE constraint is specified for any of the columns and on the
setting of the DBS Control parameter PrimaryIndexDefault (see “Primary Index Defaults” on page 211 and Utilities for details and exceptions).
Neither NoPI tables nor column‑partitioned tables can specify a permanent journal.
Nonpartitioned NoPI tables cannot specify an identity column.
Column‑partitioned tables can specify an identity column.
Hash indexes cannot be defined on NoPI or column‑partitioned tables.
SQL UPDATE (Upsert Form) requests cannot update either a NoPI or a column‑partitioned
target table.
SQL MERGE requests cannot update or insert into either a NoPI or a column‑partitioned
target table.
SQL MERGE requests can update or insert into a primary‑indexed target table from a
NoPI or column‑partitioned source table.
You cannot load rows into either a nonpartitioned NoPI or a column‑partitioned table
using the MultiLoad utility.
You can define all of the following commonly used features for both NoPI and column‑partitioned
tables:
Fallback
Secondary indexes
Join indexes
PRIMARY KEY and UNIQUE column constraints
CHECK constraints
FOREIGN KEY constraints
Triggers
XML, BLOB, and CLOB columns.
Note: Because there is normally only one row hash value per AMP for NoPI tables, there is
also a limit of approximately 256M rows per AMP for NoPI tables that contain columns
typed as XML, BLOB, or CLOB.
You can define any of the following table types as NoPI tables:
Nonpartitioned base tables
Column‑partitioned base tables (with or without row partitioning)
Column‑partitioned, single‑table, non‑aggregate, noncompressed join indexes (with
or without row partitioning)
Nonpartitioned global temporary tables
Nonpartitioned volatile tables