15.10 - ADD PRIMARY KEY (column_name) - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K
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
  • Geospatial
  • JSON
  • XML

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 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 ANSI Temporal Table Support, B035-1186 and 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 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)