What are keys? Discuss its types.
Database Management System
Computer Science Engineering
584
Deepak
In a database table, a key is a column or combination of columns that uniquely identify each row in the table.
There are several types of keys that can be used in database tables, including:
Keys are important in a database because they help to ensure data integrity and consistency, and they facilitate efficient querying and manipulation of data.
Primary key
A primary key is a column or a set of columns that uniquely identifies each row in a table. It cannot have null values, and each value must be unique. A table can have only one primary key.
Unique key:
A unique key is a column or a set of columns that must have unique values, but unlike a primary key, it can have null values.
Candidate key
A candidate key is a column or a set of columns that could potentially be used as a primary key. It must also be unique and cannot have null values.
For example: In the EMPLOYEE table, id is best suited for the primary key. The rest of the attributes, like SSN, Passport_Number, License_Number, etc., are considered a candidate key.
Super Key
Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a candidate key.
For example: In the above EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME), the name of two employees can be the same, but their EMPLYEE_ID can't be the same. Hence, this combination can also be a key.
The super key would be EMPLOYEE-ID (EMPLOYEE_ID, EMPLOYEE-NAME), etc.
Foreign key
A foreign key is a column or a set of columns in one table that refers to the primary key of another table. It is used to establish a relationship between two tables.
Alternate key
There may be one or more attributes or a combination of attributes that uniquely identify each tuple in a relation. These attributes or combinations of the attributes are called the candidate keys. One key is chosen as the primary key from these candidate keys, and the remaining candidate key, if it exists, is termed the alternate key.
In other words, the total number of the alternate keys is the total number of candidate keys minus the primary key. The alternate key may or may not exist. If there is only one candidate key in a relation, it does not have an alternate key.
For example, employee relation has two attributes, Employee_Id and PAN_No, that act as candidate keys. In this relation, Employee_Id is chosen as the primary key, so the other candidate key, PAN_No, acts as the Alternate key.
Composite key
Whenever a primary key consists of more than one attribute, it is known as a composite key. This key is also known as Concatenated Key. A composite key is a key that consists of two or more columns in a table that together uniquely identify each row.
For example, in employee relations, we assume that an employee may be assigned multiple roles, and an employee may work on multiple projects simultaneously. So the primary key will be composed of all three attributes, namely Emp_ID, Emp_role, and Proj_ID in combination. So these attributes act as a composite key since the primary key comprises more than one attribute.
Artificial key
The key created using arbitrarily assigned data are known as artificial keys. These keys are created when a primary key is large and complex and has no relationship with many other relations. The data values of the artificial keys are usually numbered in a serial order.