It is important to be able to specify how rows in a relation are distinguished conceptually, rows are distinct from one another, but from a database perspective, the difference among them must be expressed in terms of their attributes. Keys come here for a rescue.
1. Primary Key
Within a given relation, a set of one or more attributes having values that are unique within the relation and thus can uniquely identify that tuple, is said to be the primary key of the relation.
A Primary Key is a set of one or more attributes that can uniquely identify tuples within the relation.
Every relation does have a primary key. In our sample database, SuppNo is the primary key for Suppliers as it contains a unique value for each tuple in the relation. Similarly, ItemNo is the primary key for Items, and the combination of SuppNo and ItemNo is the primary key for the Shipments relation.
In some tables, a combination of more than one attribute provides a unique value for each row. In such tables, the group of these attributes is declared as the primary key. In such cases, the primary key consists of more than one attribute, it is called composite-primary-key.
The primary key is nonredundant i.e. it does not have duplicate values in the same relation.
The non-primary-key attributes of a table can be referred to as non-key attributes.
2. Candidate Key
Occasionally we may encounter a relation in which there is more than one attribute possessing the unique identification property.
All attribute combinations inside a relation that can serve as primary key are Candidate Keys as they are candidates for the primary key position.
In our sample database, there are two candidate keys SuppNo and Supp_Name in the Suppliers relation. Both of these attributes contain unique values for each tuple. Similarly, in Items, ItemNo and Item_Name are candidate keys.
In the case of two or more candidate keys, the database analyst decides one of them as the primary key for the relation.
3. Alternate Key
In the case of two or more candidate keys, only one of them serves as the primary key. The rest of them are alternates only.
A candidate key that is not the primary key is called an Alternate Key.
In the Suppliers table, Supp_Name is the alternate key and in the Items table Item_Name is the alternate key.
4. Foreign Key
A foreign key is used to represent the relationship between two tables. A foreign key is a non-key attribute (or a group of non-key attributes) whose value is derived from the primary key of another table. Or in other words, a non-key attribute of a table, which is the primary key of some other table is known as a foreign-key.
A non-key attribute, whose values are derived from the primary key of some other table, is known as Foreign-key in its current table.
The table in which this non-key attribute i.e., the foreign-key attribute exists, is called a Foreign table or Detail table, and the table that defines the Primary-key, which the foreign-key of detail-table refers to, is called Primary table or Master table.