Types of Primary Indexes | Database Design | Teradata Vantage - 17.10 - Unique and Nonunique Primary Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

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

You can define a primary index to be either unique or nonunique. The choice of the columns to use for the primary index may depend on how its rows are most frequently accessed. The uniqueness of a primary index depends on the whether or not the data in the columns is unique.

This topic describes the differences between the two types as well as their relative advantages and disadvantages.

Unique Primary Indexes

Major entities and subentities are typically assigned unique primary indexes. When a subentity has been defined in the logical model, it typically uses the same unique primary index in the physical model as the major entity it is associated with. This ensures that related rows from the two tables always hash to the same AMP.

Consider the following employee table derived from a major entity in the logical database design. The primary key is also defined as the unique primary index for the table.

employee
employee_ID employee_name home_address
PK  
UPI
6149 Joe Smith 3 Homestead Way
6171 Wei-hee Chan 44 Fifth Avenue
7049 Yuka Maeda 1000 Chestnut Lane

Always specify the unique primary index column set as NOT NULL.

Nonunique Primary Indexes

Minor entities are typically assigned nonunique primary indexes defined on the same column as the major entity with which they are associated.

Consider the following employee_phone table derived from a minor entity in the logical database design.

The employee_phone table is related to the employee table by its foreign key, employee_id, which is the primary key of the employee table. Note that the primary index for this table is defined only on the employee_id attribute, which is only half the primary key. This makes the primary index nonunique by definition.

The advantage gained by this is that both employee and employee_phone rows have the same primary index and hash to the same AMP. This means that joins on these tables, which are a frequent occurrence, do not require redistribution of table rows.

You could have defined a NUPI on phone_number because there should not be many duplicate entries for that field (husband-wife-child groupings and roommates being likely examples), but you would lose the advantage of hashing related rows to the same AMPs.

employee_phone
employee_ID phone_number phone_remarks
PK  
FK  
NUPI
6149 555-1234 Home land line. Not after 20:00.
6149 555-9315 Cell phone.
6149 555-8357 Pager.
6171 555-5678 Any time.
7049 555-9012 Never call home number.
... ... ...

Employee 6149, Joe Smith, has three different telephone numbers (rows shaded in orange) where he can be reached away from the office.

Polyinstantiation

You can create a USI for a row-level security-protected table as a composite of a row-level security constraint column and the columns of a NUPI for the table. This property can be used to implement polyinstantiation.

Polyinstantiation is a property that enables a relation to contain multiple rows with the same primary key value, where the multiple instances are distinguished by their security levels, where a security level is defined by a row-level security constraint column.

For this property not to violate the relational model, the security level instances would need to be defined as components of a composite primary key.