- Must be unique
- Must not change
- Cannot be null
The following table summarizes the differences between logical primary keys and physical primary indexes.
| Primary Key | Primary Index |
|---|---|
| Important element of logical data model. | Not used in logical data model. |
| Used to maintain referential integrity. Referential integrity is supported only on the Block File System on the primary cluster, not on the Object File System.
|
Used to distribute and retrieve data. |
| Must be unique to identify each row. | Can be unique or nonunique. |
| Values must not be changed, if you want to maintain data integrity and preserve historical relations among tables. | Values can change. |
| Cannot be null. | Can be null. |
| Does not imply access path. | Defines the most common access path. |
| Required only if referential integrity checks are to be performed. | Defined for most production tables. (Some staging tables may not have a primary index.) |
The database uses a unique primary or secondary index to enforce a primary key; therefore, the primary key can affect how Database Engine 20 distributes and retrieves rows.
The primary key is often used as the primary index for a table, but may not be the best column set to choose as a primary index. Base your primary index decision on physical database design considerations that may favor columns other than those of the primary key.
Foreign keys identify table relationships, modeling the relationship between data values across tables. A foreign key defines a column, or combination of columns, in a table. The foreign key column or columns must exist in the referenced tables as a primary key or a UNIQUE alternate key.
Database Engine 20 uses primary and foreign keys to maintain referential integrity between tables. Foreign keys are also used in join conditions.