PRIMARY KEY and UNIQUE Constraints Versus Primary Indexes
You can define the primary index for a table using either a PRIMARY KEY or a UNIQUE
constraints as the default primary index in a CREATE TABLE request.
The following bullets list the rules for defining primary keys and UNIQUE constraints
with respect to primary indexes:
A table can have at most 1 primary key and need not have a primary index.
If a table has a primary index, it can have only 1.
You cannot define a primary index and a PRIMARY KEY or UNIQUE constraint on the same
You can still define a relationship for referential integrity by referencing the UPI
of a table even if no primary key is defined explicitly for that table because it
is always valid to define a referential integrity relationship with any alternate
If both a primary index and primary key are specified in a CREATE TABLE request, then
the primary index is the hashing index and the primary key is mapped to a unique secondary
index by default.
If a primary key is specified in a CREATE TABLE request, but a primary index is not,
then the system maps the primary key to a UPI by default.
If neither primary index nor primary key is specified in a CREATE TABLE request, then
the system defines the first column that has a UNIQUE constraint as the UPI by default.
If there is no PRIMARY INDEX, PRIMARY KEY, or UNIQUE constraint defined in a CREATE
TABLE request, and the PrimaryIndexDefault parameter is set to either D or P, then
Teradata Database defines the first index‑eligible column defined for the table to
be its primary index.
The system defines this index as a NUPI by default except for the case of a single
column table defined with the SET (no duplicate rows permitted) option, in which case
the system defines it as a UPI.
If there is no PRIMARY INDEX, PRIMARY KEY constraint, or UNIQUE constraint defined
in a CREATE TABLE request, and the PrimaryIndexDefault parameter is set to N, then
Teradata Database creates the table with no primary index.
Columns defined with either of the following constraints cannot be defined to be nullable.
Columns defined with any of the following constraints can be defined as nullable;
however, you should not define any of these (or any other) table elements to be nullable unless you have
a compelling reason to do so.
UNIQUE PRIMARY INDEX
In other words, you should always explicitly declare the column set that makes up
any of these index types to be NOT NULL unless you have a compelling reason not to.
You cannot define a PRIMARY KEY or UNIQUE constraint with the same column set as a
secondary index defined on the same table.
You cannot define a UNIQUE constraint explicitly on the same columns as a PRIMARY