17.10 - PRIMARY KEY - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

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));