Define foreign key. How is this concept useful in relational data model? Illustrate with suitable example.
A foreign key is defined as a key that refers to a primary key column of another table. In other words, it is a field in a relational table that matches the primary key of another table. These keys relate different tables together in order to form an integrated database. For example, let us consider the two following tables- STUDENT table and BOOKS ISSUED table. The STUDENT table includes all the student data and the BOOKS ISSUED table includes all the books issued by the students from the school library. The basic objective here is that all the books issued must corresponds to the students that are listed in the STUDENT table. In order to do this, we need to place a foreign key in the BOOKS ISSUED table and need to relate it to the primary key of the STUDENT table.
STUDENT Table |
|
S_Id |
Name |
142 |
Noor |
135 |
Jagat |
153 |
Mohini |
BOOKS ISSUED Table |
|
Name_of_Books |
S_Id |
Economics |
135 |
Mathematics |
135 |
English |
142 |
The Join of STUDENT table and BOOKS ISSUED table appears as:
S_Id |
Name |
Name_of_Books |
135 |
Jagat |
Economics |
135 |
Jagat |
Mathematics |
142 |
Noor |
English |
Thus, we can see that in the STUDENT table, student ID (i.e. S_Id) is the primary key, whereas, in the BOOKS ISSUED table, S_Id is a foreign key, which will be used to relate the data in the STUDENT table to that of the books issued by them in the BOOKS ISSUED table.