Keys, Super Key, Alternate Key, Candidate Key, Primary Key, Composite Key, Foreign Key in Database Systems
In this tutorial, we will learn about the followings related to the database systems;
- Key
- Super Key
- Alternate Key
- Candidate Key
- Primary Key
- Composite Key
- Foreign Key
What is the key?
A key is a field that uniquely identifies a tuple in the relation. Key is used to sort the data.
What is the super key?
Super key is used to identify an attribute or set of attributes uniquely within the relation. Super key is a generalize superset of candidate keys.
What is candidate key?
Candidate keys are the set of all those attributes that can be selected as a primary key.
What is the primary key?
The primary key is the most suitable key to select a primary key.
What is the alternate key?
All those attributes that can be selected as the primary key but not selected as primary key are called alternate keys.
What is the composite key?
A composite key is a set of two or more attributes that are together selected as a composite key.
Example:
| RegistrationNo | RollNo | Name | Marks |
| 1-A1 | 1 | John | 44 |
| 1-A2 | 2 | Marry | 77 |
Table: Exams
In this example, RegistrationNo and RollNo are candidate keys. If we select RegistratinNo as the primary key then RollNo is alternate key.
If the primary key is on both RegistrationNo and RollNo then it represents a composite key.
What is the foreign key?
A foreign key is the primary key of one table that is linked with the field of another table.
In the figure, two tables are connected with each other through the foreign key.
CustomerID in left table is foreign key for CustomerID in right table
| Test Your Understandings |
1.Key not selected as primary key is called ………..?
2.Keys that can be selected as primary key are called ………..?
3. ………..key is used to link the two tables?