Difference between Primary Key and Foreign Key

Key in MySQL are the fundamental elements for constructing a relationship between two tables. They are very useful in the maintenance of a relational database structure. The main difference between them is that the primary key identifies each record in the table, whereas the foreign key is used to link two tables together. In this article, we are going to cover the essential differences between Primary and Foreign Keys based on various parameters. Before making a comparison, we will discuss in brief these keys.

What is Primary Key?

The primary key is a unique or non-null key that uniquely identifies every record in a table or relation. Each database needs a unique identifier for every row of a table, and the primary key plays a vital role in identifying rows in the table uniquely. The primary key column can't store duplicate values. It is also called a minimal super key; therefore, we cannot specify more than one primary key in any relationship.

For example, we have a table named customer with attributes such as ID, Name, and City. Only the ID column can never contain duplicate and NULL values because each customer has a unique identification number. This feature helps to identify each record in the database uniquely. Hence, we can make the ID attribute a primary key.

What is Foreign Key?

The foreign key is a group of one or more columns in a database to uniquely identify another database record in some other table to maintain the referential integrity. It is also known as the referencing key that establishes a relationship between two different tables in a database. A foreign key always matches the primary key column in another table. It means a foreign key column in one table refers to the primary key column of another table. A foreign key is beneficial in relational database normalization, especially when we need to access records from other tables.

A foreign key creates a parent-child relationship with the tables where the parent table holds the initial column values, and the child table references the parent column values. We can achieve this relationship only when the foreign key constraint is found on the child table.

For example, we have a table named contact with attributes such as ID, Customer_Id, Customer_Info, and Type. Here we can make the Customer_Id column a foreign key.

If we want to delete the referential data that removes records from both tables, we can define the foreign key in the contact table as below:

When we delete any record from the customer table, the related rows will also delete in the contact table, and both tables update automatically.

Key differences between Primary Key and Foreign Key

The following points explain the differences between primary and foreign keys:

• A primary key constraint in the relational database acts as a unique identifier for every row in the table. In contrast, a foreign key constraint establishes a relationship between two different tables to uniquely identify a row of the same table or another table.
• The primary key column does not store NULL values, whereas the foreign key can accept more than one NULL value.
• Each table in a relational database can't define more than one primary key while we can specify multiple foreign keys in a table.
• We can't remove the parent table's primary key value, which is referenced with a foreign key column in the child table. In contrast, we can delete the child table's foreign key value even though they refer to the parent table's primary key.
• A primary key is a unique and non-null constraint, so no two rows can have identical values for a primary key attribute, whereas foreign key fields can store duplicate values.
• We can insert the values into the primary key column without any limitation. In contrast, we need to ensure that the value is present in a primary key column while inserting values in the foreign key table.
• We can implicitly define the primary key constraint on temporary tables, whereas we cannot enforce foreign key constraints on temporary tables.

Primary Key vs. Foreign Key Comparison Chart

The following comparison chart explains their main differences in a quick manner:

Conclusion

In this article, we have made a comparison between primary key and foreign key constraints. Here we have concluded that both keys play an essential role in the relational database schema as they establish relations between multiple tables. The primary key column always stores the unique value for each record in the table, whereas foreign key value can be duplicated. Both constraint structure is the same, but their function differs as the primary key identifies a record in a table or relation uniquely. And the foreign key link two tables together.