Keys in Database

Keys in Database

Different types of key used in data base management system key:

A key is an attribute or set of attributes of a relation. Keys are defined in the relations for the following purposes:

For uniquely identifying the records of a relation.

For establishing relationships between relations.

For quickly accessing particular records from the relations/ table.

For sorting records of a reaction based on the data of one or more columns.

Type of Keys

Different types of keys are as follows

1.  Primary key
2.  Secondary key
3.  Candidate key
4.  Alternate key
5.  Composite key
6.  Sort key Foreign key

Primary Key

A set of attributes that uniquely identifies each record in a relation is known as primary key. In a relational database, each relation must have a

An attribute of a relation is selected as primary key when its value for each record must be unique. For example, in Employee table of an organization that has attributes emp_code, Emp_Name, Emp_Salary, and Emp_DOB etx, each employee has a unique code assigned by the organization. So the most suitable attribute of the employee table for selecting primary key is Eem_Code. It uniquely identifies each employee in the organization. On the other hand, the attribute Emp_name is not unique. More than one employee may have same hames. So it cannot be used as primary key.

Note: Please note the following ponts about primary key:

·         A relation can have only one primary key.

·         Each value in primary key much be unique

·         A Primary key cannot have null value.

Candidate Key

A relation may have more than one attribute that can be used as primary key or set of attributes that can be used as primary key is called candidate key

For example Student relation may have attribute roll no nic registration no adresss and phone no. in student relation roll no NIC and registration no are attributes that can uniquely identify that records of students so any one of these attribute can be used as primary key. Therefor these attributes are known as candidate keys

Alternate Keys

If one of the suitable candidate key is selected as the primary key of the relation, then the other candidate keys are called the alternate keys.

Suppose student relation may have candidate keys roll no NIC and registration. If nic attribute is selected as primary key then roll no and registration will be the alternate keys of student relation.

Composite Key / Concatenate key

A key that consists of two or more attributes of a relation is called composite key. It is also referred to as concatenate key.

For example in a student relation containing attributes roll no calls name address and phone. The attributes roll no and class may be selected as composite key, so that the records of all the classes of a college can be uniquely identified.

Secondary Key

A set of attributes of a relation that is used to retrieve specific data from a relation is called secondary key. It is a Non-unique field.

For example. In a student relation the class field may contain the value ics pre-medical, pre-engineering or arts in a student relation, multiple records may have value ics in class field and so on. So this field can be unseeded have specific value in class field such as the value “ICS”.

Sort Key / Control Key

The records of a table are sorted based on the data of one or more fields. An attribute or set of attributes that is used to sort the records of a table in a specific order is called sort key. It is also called control key. For example. Records of student table can be sorted based on name attribute marks attribute or by selecting both name and marks attributes as sort key etc.

Foreign Key

A foreign key is set of attributes in a table whose values must match a primary key in another table. The table that contains the foreign key I scaled dependent table or child table. Similarly, the table to which the foreign key refers called the parent table.

The foreign key is used to establish relationship between two tables. The table relationship may be one-to-one or one-to-many. For example roll-no must be an attribute of result table but not its primary key however it is the primary key that student table in result table roll no is called foreign key as shown in the following tables.