Purpose
The CREATE TABLE and CREATE JOIN INDEX statements optionally can include several modifying clauses that determine how a table or join index is created. The PRIMARY INDEX, PRIMARY AMP [INDEX], NO PRIMARY INDEX, PRIMARY KEY, UNIQUE, and PARTITION BY modifiers affect whether the table or join index includes a primary index or primary AMP index. The PrimaryIndexDefault field determines whether a table or join index that is created without any of these modifiers will have a primary index created automatically by Teradata Database, or will be created as a NoPI table, lacking a primary index.
Field Group
General
Valid Settings
| Setting | Description |
|---|---|
| D | Sets or resets this field to the Teradata default. This is the same as specifying P. |
| P | Teradata Database automatically creates primary indexes for
tables and join indexes created with CREATE TABLE and CREATE JOIN
INDEX statements that lack PRIMARY INDEX, PRIMARY AMP [INDEX], NO
PRIMARY INDEX, PRIMARY KEY, UNIQUE, and PARTITION BY modifiers. The
first column of the table serves as a nonunique primary index
(NUPI). Using the first column may
cause significant skew in the distribution of rows to the AMPs
if this column is not a good choice as a primary
index.
|
| N | Teradata Database does not create primary indexes for tables and
join indexes created without PRIMARY INDEX, PRIMARY AMP [INDEX], NO
PRIMARY INDEX, PRIMARY KEY, UNIQUE, and PARTITION BY modifiers. Defaulting to no primary index (NoPI) avoids skew that might occur with using the first column as a primary index. |
Default
D
Changes Take Effect
After the DBS Control record has been written.
Usage Notes
If a CREATE TABLE statement specifies a PARTITION BY clause but no PRIMARY INDEX, PRIMARY AMP [INDEX], or NO PRIMARY INDEX clause, the default is NO PRIMARY INDEX, regardless of the PrimaryIndexDefault setting, and regardless of whether the CREATE TABLE includes a PRIMARY KEY or UNIQUE constraint.
The best practice is to explicitly specify PRIMARY INDEX, PRIMARY AMP [INDEX], or NO PRIMARY INDEX in CREATE TABLE statements, rather than relying on the PrimaryIndexDefault field setting.
Related Topics
For the rules governing how system-defined primary indexes are created, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.