ADD PRIMARY KEY (column_name) - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
Column set is the primary key for table_name. The defined column set makes each row in the table unique. Columns in a PRIMARY KEY constraint must be defined as NOT NULL. You can specify a PRIMARY KEY constraint as a column attribute or as a table attribute. You can specify a PRIMARY KEY constraint on a single column or on a composite column set of up to 64 columns. If you specify more than one column, the primary key column set is based on the combined values of the specified columns. To add an unnamed PRIMARY KEY table constraint, use this syntax:
ADD PRIMARY KEY (column_name)
You cannot specify a PRIMARY KEY constraint for volatile tables. You cannot specify PRIMARY KEY constraints on columns with the following data types:

  • BLOB
  • CLOB
  • Period
  • XML
  • Geospatial
  • JSON
  • DATASET

You cannot specify a PRIMARY KEY constraint on a row-level security constraint column.

You can only specify one primary key per table. The primary key enforces referential constraints. To specify candidate primary keys for referential integrity relationships with other tables, use the UNIQUE column attribute. This is not necessary for Referential Constraints, but is required for standard referential integrity constraints and batch referential integrity constraints.

To enforce the constraint, the PRIMARY KEY table attribute implicitly uses a unique secondary index or UPI for nontemporal tables and a single-table join index for most temporal tables. These system-defined secondary or single-table join indexes are included in the maximum of 32 secondary and join indexes per table.

Like UNIQUE constraints, PRIMARY KEY constraints make sure of the uniqueness of alternate keys specified as part of a referential integrity relationship.

If you do not specify a primary index, the implicitly defined index is:
  • A unique primary index for a nontemporal table.
  • Not defined, and the table is a NoPI or a column-partitioned table.
If you specify a primary index, the implicitly defined index is a:
  • Secondary index for a nontemporal table.
  • System-defined single-table join index for a temporal table.

PRIMARY KEY constraints are valid for nontemporal and temporal tables.

CONSTRAINT name
To add a named PRIMARY KEY table constraint, use this syntax:
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name)