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.