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:
| Comparison Basis | Primary Key | Foreign Key |
|---|---|---|
| Basic | It is used to identify each record into the database table uniquely. | It is used to links two tables together. It means the foreign key in one table refers to the primary key of another table. |
| NULL | The primary key column value can never be NULL. | The foreign key column can accept a NULL value. |
| Count | A table can have only one primary key. | A table can have more than one foreign key. |
| Duplication | The primary key is a unique attribute; therefore, it cannot stores duplicate values in relation. | We can store duplicate values in the foreign key column. |
| Indexing | The primary key is a clustered index by default, which means it is indexed automatically. | A foreign key is not a clustered index by default. We can make clustered indexes manually. |
| Deletion | The primary key value can’t be removed from the table. If you want to delete it, then make sure the referencing foreign key does not contain its value. | The foreign key value can be removed from the table without bothering that it refers to the primary key of another table. |
| Insertion | We can insert the values into the primary key column without any limitation, either it present in a foreign key or not. | The value that is not present in the column of a primary key cannot be inserted into the referencing foreign key. |
| Temporary table | The primary key constraint can be defined on the temporary tables. | A foreign key constraint cannot be defined on the temporary tables. |
| Relationship | It cannot create a parent-child relationship in a table. | It can make a parent-child relationship in a table. |
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.