Types of Keys in Database

Branch_Id
Branch_Name
Branch_Code

Student_Id
College_Id
Rtu_Roll_No

Branch_Id

College_Id

Alternate Key in Branch_Info table:

Branch_Name
Branch_Code

Alternate Key in Student_Information table:

Student_Id
Rtu_Roll_No

Unique Key

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

Example

Possible Unique Key in Branch_Info table.

Branch_Name

Possible Unique Key in Student_Information table:

Rtu_Roll_No

Composite Key

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

Example

Composite Key in Branch_Info table.

{ Branch_Name, Branch_Code}

Composite Key in Student_Information table:

{ Student_Id, Student_Name }

Super Key

A super key is a set of one or more than one keys that can be used to uniquely identify the record in the 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 combining form of the Primary Key, Alternate key, and Unique key, and Primary Key, Unique Key, and Alternate Key are subsets of the 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 the uniqueness of the row. A super key can have a single column.

Example

Super Keys in Branch_Info Table.

​Branch_Id
Branch_Name
Branch_Code
{ Branch_Id, Branch_Code }
{ Branch_Name , Branch_Code }

Super Keys in Student_Information Table:

Student_Id
College_Id
Rtu_Roll_No
{ Student_Id, Student_Name}
{ College_Id, Branch_Id }
{ 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 words 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.

Branch_Id
Branch_Name
Branch_Code

Minimal Super Keys in Student_Information Table.

Student_Id
College_Id
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 a “Natural Key” because there is definitely a relationship between these columns and other columns that exist in the table. Natural keys are often called “Business Keys” or “Domain Keys”.

Surrogate Key

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

Example

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

Foreign Keys

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

Example

Branch_Id is a Foreign Key in the Student_Information table the primary key exists in Branch_Info(Branch_Id) table.

You can add a primary key or foreign key or unique key to an existing table using SQL. Here is a detailed article: Add Primary Key, Unique Key, Foreign Key to Existing Table using SQL.

Conclusion

The database generally only contains the Primary Key, Foreign Key, Unique Key, and Surrogate key and other remaining keys are just concepts. 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 the use of keys makes data highly reliable and provide several types of content like unique data and null values.

Thanks for reading the article.