Primary Key And Foreign Key

This is one of the most frequently asked questions in an interview: What is a primary key and what is a foreign key? Hence, I decided to write the blog on the same.
 
Primary key
  • It is used to uniquely identify each record in the table.
  • It does not allow a null value. 
Foreign key
  • Enforces database integrity.
  • A foreign key in one table points to primary key in another table.
  • The foreign key prevents invalid data from being inserted into the foreign key column.
  • The values that you enter in the foreign key column have to be one of the values contained in the table that it points to.
  • It allows the null value. 
Let us look into the practical implementation of the same concept.
 
Create two tables as tblGender and tblStudent, as shown below.
 
 
Here, ID is the primary key for tblGender and StudentId is the primary key for tblStudent.
 
Now, I have inserted some records in both the tables, as shown below.

 
As you can see, I have inserted value 99 in GenderId column in tblStudent table, which is invalid as GenderId is not present in tblGender table. It allowed us to insert the value. To avoid this, we need to make GenderId in student table as a foreign key, which points to ID column in tblGender. Thus, this will enforce the database integrity and will prevent an invalid data from being inserted into the foreign key column.
 
Here, the syntax to make GenderId as a foreign key in tblStudent table is given below.

 

We can see in the object explorer also that GenderId has been marked as a foreign key. Now, again enter some records into the table. It will not allow us to enter an invalid data.


Now, we can see the records with GenderId 1 and 2, which are inserted successfully. When I tried to enter a record with GenderId 99 or any value other than 1 and 2, it will not allow us to do, as it violates the databse integrity.Thus, foreign key allows us to prevent an invalid data to be inserted into the foreign key column.
Ebook Download
View all
Learn
View all