15.00 - PRIMARY KEY and UNIQUE Constraints Versus Primary Indexes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

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 column set.
  • 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 key.

  • 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.
  • PRIMARY KEY
  • UNIQUE
  • 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.
  • PRIMARY INDEX
  • UNIQUE PRIMARY INDEX
  • INDEX
  • UNIQUE 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 KEY constraint.