DBMS Keys: Primary, Super, Candidate, Foreign
In database management system, keys play an important role which is used for identifying unique records by the combination of one or more fields in the database table. Keys also allows you to establish the relationship between the database tables.
Different types of keys
- Primary key
- Super key
- Candidate key
- Foreign key
- Alternate key or Secondary key
- Composite key
Primary key
A primary key is a field or a set of fields in a database table that uniquely identifies each record in that table. In a table, there can be more than one candidate key from which one of the key is selected as a primary key.
- The value of the primary key field cannot be NULL.
- A table is allowed to have only one primary key.
- The value of a primary key field should always be unique.
Example:
In the Student table, Student_id can be a primary key since it is unique for each student. In the Student table, we can even select Aadhar_Number as a primary key as it is also unique.
Table: Student
| Student_id | Name | Department_id | Aadhar_Number |
Primary key
Super Key
A super key is a set of single and
multiple key attributes which is used to identify records in a table. The super
key is a superset of the candidate key.
The set of all attributes or fields to identify the tuples in a relation is
called the trivial super key.
Example:
Table Student:
| Student_id | Name | Department_id |
Following are the examples of a super key for the table Student:
1. Student_id
2. (Student_id, Name)
3. (Name, Department_id)
Candidate key
A minimal (minimum) set of attributes that can uniquely identify each record in a relation is called a candidate key. It is a subset of a super key.
- The value of the candidate key field must be unique and always be not NULL for every tuple.
- There can be more than one candidate key in a table or a relation.
- Removing any field from the candidate key fails in identifying each record uniquely.
Example:
Table student:
| Student_id | Name | Department_id |
Following are the examples of candidate key for above table:
1. Student_id
2. (Student_id, Name)
Foreign key
A foreign key is anattribute in one table that acts as a primary key in another table. The foreign key is useful for establishing the relationship between two tables in a database.
Example:
In a college, every student study in a specific department, and department and student are two different entities. So you cannot store the information of the student in the department table. That's why we relate these two tables using the primary key of one table.
Example:
We create the primary key on the field department_idof
the DEPARTMENT table.
Now in the student table, Department_id
is the foreign key, and both the tables are linked.
Alternate key
Those keys which are not selected as the primary key from the candidate keys are called as the alternate keys. These keys are also known as the secondary keys.
Example:
Table student:
| Student_id | Name | Department_id |
The example of candidate key for above table:(Student_id, Name)
Composite key
A composite key is a key which is a combination of two or more fields (attributes) that uniquely identify each record in the table.
Example:
Table student:
| Student_id | Name | Department_id |
The examples of the composite key for above table: (Name, Department_id)