A key is an attribute or set of attributes that uniquely identifies a tuple in a relation. The keys are used to access the data stored in tables quickly. The database keys are also used to create relationships between different tables.
Types of keys
Different types of keys in databases are as follows:
An attribute or set of attributes that uniquely identifies a row or record in a relation is known as primary key. Important points for a primary key are as follows:
- Only one primary key can be assigned to a relation
- The value in a primary key must be unique
- Primary key cannot contain null values
Suppose a relation student have attributes which include RegNo, Name and Class. Each student in the table is identified uniquely by the RegNo attribute. We can use the RegNo as the primary key for our table. The name attribute cannot be a primary key as the primary key is the unique name and the students may have same names.
Composite key/ Concatenate Key
The primary key that consists of two or more attributes is known as composite key. For example, the following relation uses two fields RollNo and Subject to identify each tuple. This is as example of composite key.
A relation may contain many attribute or set of attributes that can be used as primary key. The attribute or set of attributes that can be used as primary key is called candidate key.
Suppose student relation contains different attributes such as RegNo, RollNo, Name and Class. The attributes are known as candidate keys. A relation can have many candidate keys.
The keys that cannot be selected as the primary keys are called the alternative keys. The keys that cannot be selected as primary keys are usually the candidate keys. Suppose the relation student have different attributes such as RegNo, RollNo, Name and Class. The attributes RegNo and RollNo attribute is known as alternative key.
A foreign key is a key that has the same value as the primary key in another relation. A dependent or child table is a table in which the foreign key is created. The Parent table is a relation to which the foreign key is referred. The key is used to connect one relation to the other relation, when there is established a relationship between two tables. A relation may have many foreign keys.
The following figure shows two relations. The RollNo attribute in Parent relation is used as primary key. The RollNo attribute in child relation is used as foreign key. It refers to RollNo attribute in Parent relation.
An attribute or set of attributes that is basis for retrieval is known as secondary key. Secondary key can be a non unique attribute. One secondary key value may refer to many records. For example, an attribute address in Student table can be used to display all students who live in a particular country. In this case, address will be stored as secondary key.
Sort/ Control Key
An attribute or set of attributes that is used to physically sequence the stored data is called sort key. It is also known as control key. The stored data can be sorted in different ways according to the user requirement.