A primary key defines a column, or columns, that uniquely identify a row in a table. The values defining a primary key for a table:
- Must be unique
- Should 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. | Used to distribute and retrieve data. |
| Must be unique to identify each row. | Can be unique or nonunique. |
| Values should 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 Advanced SQL Engine distributes and retrieves rows.
Although the primary key is often used as the primary index for a table, it may or may not be the best column set to choose as a primary index. You should base your primary index decision on physical database design considerations that may favor columns other than those of the primary key.
A foreign key identifies table relationships. They model the relationship between data values across tables. A foreign key defines a column, or combination of columns, in a table. The foreign key column(s) must exist in the referenced tables as a primary key or a UNIQUE alternate key.
Advanced SQL Engine uses primary and foreign keys to maintain referential integrity between tables. Foreign keys are also used in join conditions.