Difference Between Primary Key vs Foreign Key
Both the primary key and foreign key are used in the relational database management systems (RDBMS), where primary key is a uniquely identified field that can be used as a reference for relating the tables in the database system. On the other hand, Foreign key is not a unique field and it is used to connect with the primary key of some other table inside the same database. Unlike primary key, a foreign key field can hold NULL values in it.
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 –
