What is the Difference Between Primary Key and Foreign Key

The main difference between primary key and foreign key is that the primary key is used to identify the records in the table uniquely while the foreign key is used to connect two table together.

Most business organizations use databases to store data. A database management system (DBMS) is a software that helps to create and manage data in databases. An advanced type of DBMS is called Relational Database Management System (RDBMS). It is based on the relational model. They store data in tables. Each table consists of rows and columns. A row represents an entry while a column represents an attribute. The tables in RDBMS have an association among them. Keys help to identify relationships between tables and to uniquely identify any row of data inside a table. A key can be a single attribute or a combination of keys. These are various types of keys, and primary key and foreign key are two of them.

Key Areas Covered

1. What is Primary Key
     – Definition, Functionality
2. What is Foreign Key
     – Definition, Functionality
3. What is the Difference Between Primary Key and Foreign Key
     – Comparison of Key Differences

Key Terms

Database, DBMS, Foreign Key, Primary Key, RDBMS

What is a Primary Key

A primary key column in the table helps to identify each row or a record in the table. It contains unique values. The primary key column cannot have Null values. A table can have a single primary key. In a Student table, the student_id is the primary key. In a Patient_Details table, the patient_id is the primary key. It is not necessary for the primary key to have a single field. It can also be a combination of multiple fields as well. When a primary key consists of multiple fields, it is called a composite key. For example, the primary key of the Student table can be the combination of both student_id and name.

What is a Foreign Key

A foreign key is used to refer to some other table. It is also called a referencing key.  It is a column or a combination of columns that matches a primary key in a different table. In other words, a foreign key in the table is a primary key of some other table.

Figure 1: Primary and Foreign Key

For example, assume that there is a Sales database. It has customer and product tables. The customer table has customer_id, name, address, and contact_no columns. The primary key of customer table is customer_id. The product has product_id, name, quality columns. The primary key of the product table is product_id. Placing the product_id in the customer table will create a link between the two tables. The product_id in product table is the primary key, but it is a foreign key in the customer_table. Likewise, it is possible to connect the tables in the database using the foreign key.

Difference Between Primary Key and Foreign Key

Definition

In the relational model of databases, a primary key is a specific choice of a minimal set of attributes or columns that uniquely specify a tuple or a row in a table. A foreign key is a field or collection of fields in one table that uniquely identifies a row of another table or the same table. Hence, this constitutes the basic difference between primary key and foreign key.

Number of Related Tables

Primary key is related to a single table while a foreign key is related to two tables. Thus, stemming out from the basic concept, this is another difference between primary key and foreign key.

Null Values

Furthermore, a primary key value cannot be null but, a foreign key value can be null.

Duplicate Values

Also, another difference between primary key and foreign key is that the primary key values cannot have duplicate values whereas the foreign key values can have duplicate values.

Number of Keys

Moreover, one other important difference between primary key and foreign key is that there can only be a single primary key in a table. However, there can be multiple foreign keys in a table.

Usage

Primary key is used to identify the records of the table uniquely while foreign key is used to link two tables together. This is the main difference between primary key and foreign key.

Conclusion

The primary key and foreign key are two types of keys used in RDBMS. The difference between primary key and foreign key is that the primary key is used to identify the records in the table uniquely while the foreign key is used to connect two table together.   

Reference:

1. “SQL Primary Key.” Www.tutorialspoint.com, Tutorials Point, Available here.
2. “SQL Foreign Key.” Www.tutorialspoint.com, Tutorials Point, Available here.

Image Courtesy:

1. “Star-schema-example” By SqlPac (talk) – (CC BY-SA 3.0) via Commons Wikimedia