There are two types of keys: primary and foreign. A primary key is a column (or group of columns) whose value uniquely identifies each row in a table. Because the key value is always unique, you can use it to detect and prevent duplicate rows. A good primary key has the following characteristics:
It is
unique. For example, the social security column in an Employee or Student table is an example of an unique key because it uniquely identifies each individual. The Cust Number column in the Customer table uniquely identifies each customer. It is not practical to use a person's name as an unique key because more than one customer might have the same name. Also, databases do not detect variations in names as duplicates (for example, Cathy for Catherine, Joe for Joseph). Furthermore, people do sometimes change their names (for example, through a marriage or divorce).
It is
stable; that is, it is unlikely to change. A social security number is an example of a stable key because but it is unlikely to change, while a person's or customer's name might change.
It is
short; that is, it has few characters. Smaller columns occupy less storage space, database searches are faster, and entries are less prone to mistakes. For example, a social security column of nine digits is easier to access than a name column of 30 characters.
A
foreign key is a column value in one table that is required to match the column value of the primary key in another table. In other words, it is the reference by one table to another. If the foreign key value is not null, then the primary key value in the referenced table must exist. It is this relationship of a column in one table to a column in another table that provides the relational database with its ability to join tables.
Database Design describes this concept in more detail.
When either a primary key or foreign key is comprised of multiple columns, it is considered a composite key.
