PRIMARY KEY and UNIQUE Constraints Versus Primary Indexes - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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 statement.

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 statement, 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 statement, 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 statement, 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 statement, 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 statement, 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:
    • PRIMARY INDEX
    • UNIQUE PRIMARY INDEX
    • INDEX
    • UNIQUE INDEX

      You should declare the column set that constitutes these index types to be NOT NULL unless there is 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.