PRIMARY KEY and UNIQUE Constraints Versus Primary Indexes - Analytics Database - 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.