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 the data in the columns is unique.
This topic describes the differences between the two types and their relative advantages and disadvantages.
Unique Primary Indexes
Major entities and subentities are typically assigned unique primary indexes. A subentity defined in the logical model typically uses the same unique primary index in the physical model as the associated major entity. This makes sure that related rows from the two tables 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 |
| … | … | … |
Specify the unique primary index column as NOT NULL.
Nonunique Primary Indexes
Minor entities are typically assigned nonunique primary indexes defined on the same column as the associated major entity.
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. 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. Therefore, joins on these tables, which are a frequent occurrence, do not require redistribution of table rows.
Alternatively, you can define a NUPI on phone_number because there are unlikely to be duplicate entries for that field (husband-wife-child groupings and roommates being likely examples), but you 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 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 must be defined as components of a composite primary key.