PRIMARY KEY - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

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
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

A column or column set is the primary key for table_name. The defined column set makes each row in the table unique. The primary key is also used to enforce referential constraints.

The column must be defined as NOT NULL.

You can specify a PRIMARY KEY constraint as a:
  • column attribute for a single column or a column set
  • table attribute, except for volatile tables
You cannot specify PRIMARY KEY constraints on columns with these data types:
  • BLOB
  • BLOB UDT
  • CLOB
  • CLOB UDT
  • VARIANT_TYPE
  • ARRAY
  • VARRAY
  • Period
  • XML
  • Geospatial
  • JSON
  • DATASET

A table can only have one primary key. 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.

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

For a PRIMARY KEY constraint, nontemporal tables use a system-defined secondary index and temporal tables use a single-table join index. System-defined secondary or single-table join indexes used for this constraint count toward a maximum of 32 secondary, hash, and join indexes per table.

Like UNIQUE constraints, PRIMARY KEY constraints ensure that the uniqueness of alternate keys is enforced when they are specified in a referential integrity relationship.

When a PRIMARY KEY constraint is defined for a normalized table, Vantage validates the constraint with normalized rows. If the normalized row violates the PRIMARY KEY constraint, the system returns an error to the requestor.

For an unnamed PRIMARY KEY column constraint, use this syntax:

PRIMARY KEY
CONSTRAINT constraint_name
For a named PRIMARY KEY column constraint, use this syntax:
CONSTRAINT constraint_name PRIMARY KEY
Use the PRIMARY KEY column attribute to apply the constraint to a single column.
See Teradata Vantage™ - SQL Fundamentals, B035-1141 for the rules for naming database objects.

Example: Specifying Table-Level Named CHECK, PRIMARY KEY, UNIQUE, and FOREIGN KEY Constraints

The request in this example names constraints at the table level.

CREATE TABLE good_2 (
  column_1 INTEGER NOT NULL,
  column_2 INTEGER NOT NULL,
  column_3 INTEGER NOT NULL,
  column_4 INTEGER NOT NULL,
  column_5 INTEGER,
  column_6 INTEGER, 
  CONSTRAINT primary_1
  PRIMARY KEY (column_1, column_2),
  CONSTRAINT unique_1
  UNIQUE (column_3, column_4),
  CONSTRAINT check_1
  CHECK (column_3 > 0 OR column_4 IS NOT NULL),
  CONSTRAINT reference_1
  FOREIGN KEY (column_5, column_6) 
  REFERENCES parent_1 (column_2, column_3));

Example: Specifying a Mix of Column-Level and Table-Level Named and Unnamed PRIMARY KEY, UNIQUE, and FOREIGN KEY Constraints

This statement defines named and unnamed constraints at the column and table levels.

CREATE TABLE good_3 (
  column_1 INTEGER NOT NULL
  CONSTRAINT primary_1 
  PRIMARY KEY,
  column_2 INTEGER NOT NULL
  CONSTRAINT unique_1 UNIQUE
  CONSTRAINT check_1
   CHECK (column_2 <> 3)
  CONSTRAINT reference_1
   REFERENCES parent_1
   CHECK (column_2 > 0)
   REFERENCES parent_1 (column_4),
  column_3 INTEGER NOT NULL,
  column_4 INTEGER NOT NULL,
  column_5 INTEGER,
   CONSTRAINT unique_2 UNIQUE (column_3),
   CONSTRAINT check_2
   CHECK (column_3 > 0 AND column_3 < 100),
   CONSTRAINT reference_2
 FOREIGN KEY (column_3)
 REFERENCES parent_1 (column_5), UNIQUE (column_4),
   CHECK (column_4 > column_5),
 FOREIGN KEY (column_4, column_5)
 REFERENCES parent_1 (column_6, column_7));