ADD PRIMARY KEY (column_name) - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples
Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™
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
  • BLOB UDT
  • CLOB
  • CLOB UDT
  • VARIANT_TYPE
  • ARRAY
  • VARRAY
  • 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. For details, see Teradata Vantage™ - Temporal Table Support , B035-1182 . These system-defined secondary or single-table join indexes are included in the maximum of 32 secondary, hash, and join indexes per table.

Like UNIQUE constraints, PRIMARY KEY constraints ensure that the uniqueness of alternate keys when they are 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. For details, see Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ - Temporal Table Support , B035-1182 .

PRIMARY KEY constraints are valid for nontemporal and temporal tables. For details about temporal tables with PRIMARY KEY constraints, see Teradata Vantage™ - Temporal Table Support , B035-1182 .

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