A key is a field that uniquely identifies a tuple in the relation. The 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. The super key is a generalize superset of candidate keys.
What is a 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.
The primary key has the following properties;
- The primary key values must be unique.
- The primary key values must be given a value when a new record is inserted.
- The primary key values cannot be NULL.
What is the alternate key?
All those attributes that can be selected as the primary key but not selected as primary keys 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 of composite key:
| 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 an 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 the left table is a foreign key for CustomerID in the right table.
- Foreign Key references the primary key of another table. It helps to build the relationship between Tables.
- A foreign key can have a different name from its primary key.
- A foreign key ensures rows in one table have corresponding rows in another table.
- Primary key values must be unique but it is not compulsory for values stored in the attributes with a foreign key.
- Foreign key’s values can be null.
| 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?
Foreign Key behaving differently on Cascade strict and SET Null
Topic Covered: super key and candidate key | super key vs primary key | candidate key vs primary key | | Foreign key vs primary key. | super key vs composite key | what is candidate key in DBMS | alternate | keys in DBMS | foreign key in DBMS | composite key in DBMS | Types of keys in DBMS.