15.00 - Nonunique Primary Indexes - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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 across the BYNET.

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 6149, Joe Smith, has three different telephone numbers (rows shaded in red) where he can be reached away from the office.