Types Of Keys In Database

Introduction about Key

A Key is a data item that exclusively identifies a record. In other words, key is a set of column(s) that is used to uniquely identify the record in a table. It is used to fetch or retrieve records / data-rows from data table according to the condition/requirement. Key provide several types of constraints like column can’t store duplicate values or null values. Keys are also used to generate relationship among different database tables or views.

Types of Keys

Database supports the following types of keys.

• Super Key
• Minimal Super Key
• Candidate Key
• Primary Key
• Unique Key
• Alternate Key
• Composite Key
• Foreign Key
• Natural Key
• Surrogate Key

Now we take two tables for better understanding of the key. First table is “Branch Info” and second table is “Student_Information”.





Now we read about each key.

Candidate Key

A Candidate key is an attribute or set of attributes that uniquely identifies a record. Among the set of candidate, one candidate key is chosen as Primary Key. So a table can have multiple candidate key but each table can have maximum one primary key.

Example:

Possible Candidate Keys in Branch_Info table.

1. Branch_Id
2. Branch_Name
3. Branch_Code

Possible Candidate keys in Student_Information table.

1. Student_Id
2. College_Id
3. Rtu_Roll_No

Primary Key

A Primary key uniquely identifies each record in a table and must never be the same for the 2 records. Primary key is a set of one or more fields ( columns) of a table that uniquely identify a record in database table. A table can have only one primary key and one candidate key can select as a primary key. The primary key should be chosen such that its attributes are never or rarely changed, for example, we can’t select Student_Id field as a primary key because in some case Student_Id of student may be changed.

Example:

Primary Key in Branch_Info table:

1. Branch_Id

Primary Key in Student_Information Table:

1. College_Id

Alternate Key:

Alternate keys are candidate keys that are not selected as primary key. Alternate key can also work as a primary key. Alternate key is also called “Secondary Key”.

Example:

Alternate Key in Branch_Info table:

1. Branch_Name
2. Branch_Code

Alternate Key in Student_Information table:

1. Student_Id
2. Rtu_Roll_No

Unique Key:

A unique key is a set of one or more attribute that can be used to uniquely identify the records in table. Unique key is similar to primary key but unique key field can contain a “Null” value but primary key doesn’t allow “Null” value. Other difference is that primary key field contain a clustered index and unique field contain a non-clustered index.

Example:

Possible Unique Key in Branch_Info table.

1. Branch_Name

Possible Unique Key in Student_Information table:

1. Rtu_Roll_No

Composite Key:

Composite key is a combination of more than one attributes that can be used to uniquely identity each record. It is also known as “Compound” key. A composite key may be a candidate or primary key.

Example:

Composite Key in Branch_Info table.

1. { Branch_Name, Branch_Code}
   
   Composite Key in Student_Information table:
   
   
2. { Student_Id, Student_Name }

Super Key

Super key is a set of on e or more than one keys that can be used to uniquely identify the record in table. A Super key for an entity is a set of one or more attributes whose combined value uniquely identifies the entity in the entity set. A super key is a combine form of Primary Key, Alternate key and Unique key and Primary Key, Unique Key and Alternate Key are subset of super key. A Super Key is simply a non-minimal Candidate Key, that is to say one with additional columns not strictly required to ensure uniqueness of the row. A super key can have a single column.

Example:

Super Keys in Branch_Info Table.

1. Branch_Id
2. Branch_Name
3. Branch_Code
4. { Branch_Id, Branch_Code }
5. { Branch_Name , Branch_Code }

Super Keys in Student_Information Table:

1. Student_Id
2. College_Id
3. Rtu_Roll_No
4. { Student_Id, Student_Name}
5. { College_Id, Branch_Id }
6. { Rtu_Roll_No, Session }

Minimal Super Key:

A minimal super key is a minimum set of columns that can be used to uniquely identify a row. In other wordsm the minimum number of columns that can be combined to give a unique value for every row in the table.

Example:

Minimal Super Keys in Branch_Info Table.

1. Branch_Id
2. Branch_Name
3. Branch_Code

Minimal Super Keys in Student_Information Table:

1. Student_Id
2. College_Id
3. Rtu_Roll_No

Natural Keys:

A natural key is a key composed of columns that actually have a logical relationship to other columns within a table. For example, if we use Student_Id, Student_Name and Father_Name columns to form a key then it would be “Natural Key” because there is definitely a relationship between these columns and other columns that exist in table. Natural keys are often called “Business Key ” or “Domain Key”.

Surrogate Key:

Surrogate key is an artificial key that is used to uniquely identify the record in table. For example, in SQL Server or Sybase database system contain an artificial key that is known as “Identity”. Surrogate keys are just simple sequential number. Surrogate keys are only used to act as a primary key.

Example:

Branch_Id is a Surrogate Key in Branch_Info table and Student_Id is a Surrogate key of Student_Information table.

Foreign Keys:

Foreign key is used to generate the relationship between the tables. Foreign Key is a field in database table that is Primary key in another table. A foreign key can accept null and duplicate value.

Example:

Branch_Id is a Foreign Key in Student_Information table that primary key exist in Branch_Info(Branch_Id) table.

Conclusion

Database generally only contain Primary Key, Foreign Key, Unique Key and Surrogate key and other remaining keys are just concept. A table must have a unique key. According to Dr. E. F. Codd ‘s third rule “Every single data element (value) is guaranteed to be accessible logically with a combination of table-name, primary-key (row value), and attribute-name (column value)”. So each table must have keys , because use of keys make data highly reliable and provide several types of content like unique data and null values.

Thanks for reading the article.