Difference Between Foreign key and Primary key

The key difference between foreign key and primary key is that foreign key is a column or a set of columns that refer to a primary key or a candidate key of another table while primary key is a column or a set of columns that can be used to uniquely identify a row in a table.

A column or a set of columns that can be used to identify or access a row or a set of rows in a database is called a key. A primary key in a relational database is a combination of columns in a table that uniquely identify a row of the table. Foreign key in a relational database is a field in a table that matches the primary key of another table. The foreign key is used to cross reference tables.

CONTENTS

1. Overview and Key Difference
2. What is Foreign key
3. What is Primary key
4. Side by Side Comparison – Foreign key and Primary key in Tabular Form
5. Summary

What is Foreign key?

Foreign key is a referential constraint between two tables. It identifies a column or a set of columns in one table, called the referencing table that refers to a set of columns in another table, called the referenced table. The foreign key or the columns in the referencing table must be the primary key or a candidate key (a key that can be used as the primary key) in the referenced table. Moreover, foreign keys allow linking data across several tables. Therefore, the foreign key cannot contain values that do not appear in the table that it refers to. Then the reference provided by the foreign key helps to link information in several tables and this would become essential with normalized databases. Multiple rows in the referencing table may refer to a single row in the referenced table.

Figure 01: Foreign Key Mapping

In ANSI SQL standard, the FOREIGN KEY constraint defines foreign keys.  Furthermore, it is possible to define the foreign keys when creating the table itself.  A table can have multiple foreign keys, and they can reference different tables.

What is Primary key?

Primary key is a column or a combination of columns that uniquely defines a row in a table of a relational database. A table can have at most one primary key. Primary key enforces the implicit NOT NULL constraint. Therefore, a column with primary key cannot have NULL values in it. Primary key can be a normal attribute in the table that is guaranteed to be unique such as a social security number, or it could be a unique value generated by the database management system such as a Globally Unique Identifier (GUID) in Microsoft SQL Server.

Figure 02: Primary Key

Furthermore, the PRIMARY KEY constraint in ANSI SQL Standard defines primary keys.  It is also possible to define a primary key when creating the table. In addition to that, SQL allows primary key to be made up of one or more columns, and each column that is included in the primary key is implicitly defined to be NOT NULL. But some database management systems require making the primary key columns explicitly NOT NULL.

What is the Difference Between Foreign key and Primary key?

Summary – Foreign key vs Primary key

The difference between foreign key and primary key is that foreign key is a column or a set of columns that refer to a primary key or a candidate key of another table while primary key is a column or a set of columns that can be used to uniquely identify a row in a table.

Reference:

1.“Foreign Key.” Wikipedia, Wikimedia Foundation, 12 May 2018. Available here  
2.“Primary Key.” Wikipedia, Wikimedia Foundation, 3 Apr. 2018. Available here 

Image Courtesy:

1.’Foreign-Key-Mapping’By MovGP0 – Own work, (CC BY-SA 3.0) via Commons Wikimedia  
2.’Erd-id-as-primary-key’By UniCollab – Own work, (CC BY-SA 3.0) via Commons Wikimedia  

Related posts:

Difference Between Primary key and Unique key Difference Between SQL Server 2008 and Express Difference Between RDBMS and ORDBMS Difference Between DBMS and Data Warehouse Difference Between Explicit Cursor and Implicit Cursor