What you will gain from this blog post?

If you are one who is struggling to get the concept of foreign key and referential integrity to stick in your mind, then let me tell you that you are at the right place, because after reading visually appealing blog post you will truly understand the concept of foreign key and how it can be used to maintain referential integrity. So without any further due let's jump straight into the content.

What is a foreign key 🔑

First of all the thing which you must know is that referential integrity is one of the favorite topics from which the interviewers asks questions so , before understanding that concept you must be completely aware about foreign key .

Foreign key is simply is an attribute or set of attributes which takes reference from the primary key of the same table or from another table .

In the definition of the foreign key , you would observe that it is defined that foreign key can be set of attributes , but what do we mean by set of attributes ? , So basically the answer to this query is that a composite attribute( which is simply an attribute that can be further divided into components ) can also act as a foreign and it is not a hard and fast rule that only simple attributes can be the foreign keys. But at the same time the thing which you also must know is that there can also be multiple foreign keys in the database .

Visually understand the concept of foreign key

While studying about any topic , it is always observed that the content which contains more visuals , leads to better retaining . Thus in order to deeply understand the foreign key , let us consider a situation where our database contains data regarding the students who are working individually on a particular project . Now since there are multiple students who are working individually on a single project thus this will be a One to One relationship.

In the visual above as you can see that both the student and project entities have respective attributes. So when we will convert the ER model into the relational model then in the relational model of the works on relation both the student id and project id will be the foreign key, thus it is totally up to us which one to choose as the primary key for the relational model of the relation between student and the project entity.

Now let us suppose that we chose project id as the primary key , thus now we will reduce the number of tables by adding project id as an attribute in the relatonal model of student entity.

What is difference between referenced and referencing table ?

After reducing the tables, the relational model of the student entity will be called as referencing table , whereas the relational model of the project entity will be called as the referenced table. The reason behind why student is called only as referencing table and not the referenced table , is because the table which contains the primary key is called the referenced table whereas the table which contains the foreign key is called the referencing table.

What is referential integrity ?

Referential integrity which is subset of data integrity means maintaining the accuracy and consistency of data within a relationship or in simple words we can say that by in referential integiry we try to make sure that there are no invalid links between the tables in the database.

Now the thing is that in database , in order to build the realationship among the data stored in different table foriegn key is used , which take reference from table having primary key , now since there is relationship between data stored in multiple tables , thus there must be referential integrity and for that we have different method which we can use , but before discussing those methods let us try to understand that what would happen if there is not referential integrity.

In order to better understand the importance of referential integrity , let us assume we have a database containing data related to the students and course which they are studying in the university. As per the data stored in the database , every student is studying only one unique course , thus it would be one to one relationship .

For referenced table

Now if we will try to add new data in the referenced table ( which can be any table out of student and course , but in this case we care considering the student table to be referenced table ) then there will be no loss of referential integrity , but at the same time if we will try to remove previously added data linked to other tables using foreign key , then there can be violation of referntial integrity , because if the data is linked to other table using the foreign key , then the data will only be removed from referenced table , but not from the referencing table.

Now since we know that the referential integrity may get harm , if we will try to remove the data from referenced table , but how can we solve this problem of loss of referential integrity while deleting the data from referenced ?

The answer to this query is that there are simply 2 ways :

1. On delete Cascade : If we will use this method , and delete some data from the referenced table then all the tables related to the referenced table , in those tables the related data will be automatically removed.
2. On delete set null : If we will use this method , and will delete some data from the refereced table , then all the tables related to the referenced table , in those tables the related data will be set to null.

Now the things to remember is that , in some case the foreign key in the table might also act as primary key , thus if we will use ( On delete null ) method in order to prevent the loss of referential integrity , then we might loose the unique identity of the data stored in database .

Now apart from the insertion and deletion of data from the refernced table , the updation of data may also cause loss of referential integrity.

For referencing table

In case of referencing table , the insertion of data may cause of loss of referential integrity . To better understand let us assume that we have added the data in the referencing table , that a student having id C4 is studying DBMS , but since the new data is added only the referencing table and not in the referenced table , thus it would mean that the student has not taken admission in the college but is studying the course with course id C4, which is leading to loss of referential integrity.

But contrary to the insertion of data in the referencing table , the deletion of data from the referencing table will not cause any loss in the referential integrity. ⬇️

At last the updation of data in the referencing table , may cause loss of referential integrity . To better understand let us suppose we updated the id of student from S1 to S4 , now this updation will definitely cause loss of referential integrity , because in the referencing table , there would be some student with id S4 , but there would be no such student in the referenced table .