PRIMARY KEY and UNIQUE Constraints Versus Primary Indexes - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
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 Vantage 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 Vantage 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.