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 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 depends on whether a PRIMARY KEY or UNIQUE constraint is specified for any of the columns .
- 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.Identity column is supported on both the Block File System and Object File System. However, you can only run queries related to an identity column table on the primary cluster.
- 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.
- 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. Because there is typically 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