Keys in DBMS
A key in a database is an attribute on a table column that is used to identify a row or record in a set of rows. In other words, a key in DBMS is a column(s) that is used to uniquely identify the record in a table. In a relational database, data/records are stored in a tabular format, a set of rows and columns. It is used to fetch or retrieve records/data rows from the data table according to the condition/requirement. A key provides several types of constraints like a column that can’t store duplicate values or null values. Keys are also used to generate relationships among different database tables or views. Database keys are also used to uniquely identify a row in a database table by a combination of one or more columns in that table.
![Database key types]()
DBMS supports multiple types of keys and each of these types has a different purpose and use case. This post explains the different types of keys in databases, their meanings, and how to set and use these different types of keys in SQL.
Types of Keys
Databases support ten different types of keys in DBMS and each key has different functionality. The following is a list of ten different types of keys in DBMS.
- Super Key
- Minimal Super Key
- Candidate Key
- Primary Key
- Unique Key
- Alternate Key
- Composite Key
- Foreign Key
- Natural Key
- Surrogate Key
To understand these different types of keys better, let's start with database tables. We take two tables for a better understanding of the key. The first table is “Branch Info” and the second table is “Student_Information”.
![Branch Info]()
![Table]()
Now we learn about each of these keys and how to use them.
Candidate Key
A candidate key is an attribute or set of attributes that uniquely identifies a record. Among the set of candidates, one candidate key is chosen as the Primary Key. So a table can have multiple candidate keys but each table can have only one primary key.
Example
Possible Candidate Keys in Branch_Info table.
Branch_Id
Branch_Name
Branch_Code
Possible Candidate keys in Student_Information table.
Student_Id
College_Id
Rtu_Roll_No
Primary Key
A primary key uniquely identifies each record in a table and must never be the same for two records. The primary key is a set of one or more fields ( columns) of a table that uniquely identify a record in a 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 the Student_Id field as a primary key because in some cases Student_Id of a student may be changed.
Example
Primary Key in Branch_Info table:
Branch_Id
Primary Key in Student_Information Table:
College_Id
Here is a detailed article on primary keys: Primary Keys in SQL Server.
Alternate Key
Alternate keys are candidate keys that are not selected as the primary keys. The alternate key can also work as a primary key. The alternate key is also called the “Secondary Key”.
Example
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.