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_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_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.