Difference Between Primary Key vs Foreign Key
In a Relational Database Management System (RDBMS), the data is stored in tables. As a huge amount of data gets stored in the tables, there is always a possibility that the data may become unorganized or contain duplicates. Also when we need to retrieve the data according to our requirement from that huge data, the task becomes difficult. The concept of Key is used in the RDBMS to identify records in the table from thousands of rows of data. Also, the usage of Key helps in establishing the relationship among the tables so that the data can be referred from among various tables. So the Key in Relational Database Management System maintains table-level integrity as well as referential integrity. In this article, we will examine in detail the difference between the primary key vs the foreign key.
The Primary Key in a table identifies records uniquely and the Foreign Key refers to the Primary Key of another table. Let us take the example of two tables: Employee and Salary tables to understand the Primary and Foreign Key concepts. If the Employee table has the Primary Key as Employee_ID, then a column Employee_ID must be present in the Salary table as it needs to have a relationship between these two tables by having a Foreign Key in the Salary table which can refer to the Primary Key of the Employee table.
Head to Head Comparison between Primary Key vs Foreign Key (Infographics)
Below are the top 6 differences between Primary Key vs Foreign Key:
Key Differences Between Primary Key vs Foreign Key
Let us discuss some of the major key differences between Primary Key vs Foreign Key:
- The Primary Key identifies the records in a table uniquely whereas the Foreign Key is used to link the tables i.e. it refers to the Primary Key of another table.
- The Primary Key column in a table cannot have Null values and should always have unique values. But the Foreign Key in the table can contain Null values and also can have duplicate values.
- A table can have only one Primary Key whereas there can be more than one Foreign Key for a table.
- The index gets created for the Primary Key automatically whereas, for the Foreign Key, indexes do not get created automatically.
- It is easy to delete the Foreign Key constraint though it refers to the Primary Key. But the deletion of Primary key constraint may create records with child records having no parent record.
Comparison Table of Primary Key vs Foreign Key
The table below summarizes the comparisons between Primary Key vs Foreign Key:
| Primary Key | Foreign Key |
| The Primary Key of a table in RDBMS can be a single column or may comprise of more than one column which is used to identify each row in a table uniquely. In the case of the Primary Key consisting of more than one column, the data from each column is used to decide the uniqueness of a row. A table in RDMBS can have only one Primary Key. | The Foreign Key can be a single column or may consist of more than one column in a table which is used to refer to the Primary Key of another table. The Foreign Key can be used to link the tables in RDBMS. Unlike the Primary Key attribute, a table in RDBMS can have more than one Foreign Key. |
| There are few conditions which should be satisfied for a column to be a Primary Key in a table. One of the conditions is that the Primary Key should contain a unique value for each record. So a table in RDBMS cannot be allowed to have duplicate values for two rows for the Primary Key attribute. | Unlike the Primary Key of a table in RDBMS, the Foreign Key can contain duplicate values. This allows having duplicate values for the rows of a table for the foreign key attribute. |
| The other condition of a column satisfying to be a Primary Key is that it should not contain Null values. | But the Foreign Key of a table in RDBMS can contain Null values. |
| A Primary Key is specified while defining the table and it is compulsory to define the Primary Key. | Unlike the Primary Key, there is no specific table definition for denoting a Key to be Foreign Key. |
| It is not possible to delete the Primary Key constraint from a Parent Table with the Foreign Key of the Child Table referring to it. So the Child Table needs to be deleted first before deleting the Parent table. | But if we need to delete the Foreign Key constraint in a table, it can be deleted from the Child Table even though it refers to the Primary Key of the Parent Table.
|
| The Primary Key is indexed automatically i.e. the Primary Key gets stored in an index for a table and this index helps in administering the requirement of uniqueness and because of this, the foreign key values are easily referred to the corresponding Primary Key values. | In the case of a Foreign Key, the indexes do not get created automatically and usually, the Database Administrator defines them. |
Conclusion
The Primary and Foreign Keys not only implement different types of integrity but also create relationships among tables. The Primary and Foreign keys also ensure that the columns created for the relationship must contain matching values and unique records are maintained in the table. The Relational Database Management System works efficiently because of the presence of the Primary and the Foreign Key.
The Primary Key constraint helps in applying the data integrity automatically as it prevents from inserting or updating duplicate row of data. By using the key constraints also it prevents deleting the row from the primary key table from the parent table so that no such child records are created for which there is no parent record. Also when a row in reference to the primary key is deleted or updated in the parent table, the referential integrity is maintained by the Foreign Key. The Keys have a vital role in establishing links among the database tables and also within the table.
Recommended Articles
This is a guide to Primary Key vs Foreign Key. Here we discuss the Primary Key vs Foreign Key key differences with infographics and comparison table. You can also go through our other suggested articles to learn more –
