15.00 - Keys - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Keys

A primary key is a column, or combination of columns, in a table that uniquely identifies each row in the table. The values defining a primary key for a table:

  • Must be unique
  • Cannot change
  • Cannot be null
  • A foreign key is a column, or combination of columns, in a table that is also the primary key in one or more additional tables in the same database. Foreign keys provide a mechanism to link related tables based on key values.

    Keys and Referential Integrity

    Teradata Database uses primary and foreign keys to maintain referential integrity. For additional information, see “Referential Integrity” on page 46.

    Effect on Row Distribution

    Because Teradata Database uses a unique primary or secondary index to enforce a primary key, the primary key can affect how Teradata Database distributes and retrieves rows. For more information, see “Primary Indexes” on page 33 and “Secondary Indexes” on page 36.

    Primary Keys and Primary Indexes

    The following table summarizes the differences between keys and indexes, using the primary key and primary index for purposes of comparison.

     

    Primary Key

    Primary Index

    Important element of logical data model.

    Not used in logical data model.

    Used to maintain referential integrity.

    Used to distribute and retrieve data.

    Must be unique to identify each row.

    Can be unique or nonunique.

    Values cannot change.

    Values can change.

    Cannot be null.

    Can be null.

    Does not imply access path.

    Defines the most common access path.

    Not required for physical table definition.

    Required for physical table definition.