The following table describes some of the relationships between PKs and PIs.
Primary Key | Primary Index |
---|---|
Identifies a row uniquely. | Distributes rows. |
Does not imply access path. | Defines most common access path. |
Must be unique. | May be unique or nonunique. |
May not be null. | May be null. |
Causes a Unique Primary Index (UPI) or Unique Secondary Index (USI) to be created. | N/A |
Constraint used to ensure referential integrity. | Physical access mechanism. |
Required by Teradata Database only if referential integrity checks are to be performed. | Defined for most production tables. Some staging tables may not have a primary index (NoPI table). |
|
64-column limit. |
Values should not be changed if you want to maintain data integrity and preserve historical relations among tables. | Values can be changed. |
The columns chosen for the UPI of a table are frequently the same columns identified as the PK during the data modeling process, but no hard-and-fast rule makes this so. In fact, physical database design considerations often lead to a choice of columns other than those of the primary key for the PI of a table.